Sql-Server

Occasionally I run into a need to clear out all objects in a schema in a SQL Server database. Sometimes it’s because I want to remove the schema itself. It’s often painful to do as I have to find all the types of T-SQL objects, and then often have to remove them in a particular order.

So I added a tool to my SDU Tools collection to do just that.

2017-11-03

The on-premises versions of SQL Server have the ability to connect one server to another via a mechanism called Linked Servers.

Azure-based SQL Server databases can communicate with each other by a mechanism called External Tables. I’ll write more about External Tables soon.

With Linked Servers though, I often hear people describing performance problems and yet there’s a configuration setting that commonly causes this. In Object Explorer below, you can see I have a Linked Server called PARTNER.

2017-11-02

SQL Server Management Studio (SSMS) is a wonderful tool but there are so many ways built-in shortcuts that many users don’t seem to be aware of.

A simple example, is if I want to script all the indexes on a table (or all the tables, all the stored procedures, etc, etc.). Yes you can do that the long way by using the Generate Scripts option but there’s a better way.

2017-11-01

I recently shipped the latest update to my SDU Tools collection.

This time I decided to add a tool that’s in the “cute” category. It’s called InvertString and you can see it action in the image above.

Yes, it takes an input string and returns a string that looks quite like an upside down version of the same string.

As part of the inversion I also reversed the order of the string so when you look at it upside down, it still looks correct. But if what you really want is a mirror image, you could combine it with REVERSE to achieve that outcome:

2017-10-31

Most SQL Server developers are aware that they can create computed columns. We do that by defining a column AS some expression like this:

Each time the value from that column is queried, the calculation is performed so the result can be returned. This makes sense when the value is changing regularly and the value is queried infrequently.

However, according to my completely subjective statistics, most computed columns are queried much more than they are ever changed. So why work the value out each and every time?

2017-10-30

I spend most of my life in a variety of customer sites. Most of these nowadays tend to be large financial organizations. When I talk to developers in those organizations, and ask them about how they interact with their DBAs, I can’t tell you how often I hear that they try to avoid ever making schema changes, etc. as it takes too long and the process is painful.

When I talk to the DBAs at the same organizations, and I ask them why they resist making schema changes, I hear this:

2017-10-27

GUIDs (uniqueidentifiers in SQL Server) are well understood to cause fragmentation issues when used as clustering keys for SQL Server tables. It’s easy to understand why, but is NEWSEQUENTIALID the answer? I don’t think so. Here’s why.

When a SQL Server table has a clustered index, it needs to maintain the logical sequence of rows in the order of the clustering key. There are a few important notes here:

• I’ve mentioned key, not column. The key might involve more than one column.
• I’m not talking about primary keys. While the default for SQL Server is to make primary keys clustered if there isn’t already another clustering key, primary keys are a logical construct unrelated to how the table data is stored.
• I’m not talking about physical storage. I often hear people say that clustered indexes determine how data is physically stored. That was in old Microsoft courseware, etc. and just isn’t true. It’s about logical ordering. If not, there would be no fragmentation and changing the data would take forever.
• The problem with using GUIDs as clustering keys is that they are typically generated in a random order using a function like NEWID() or using a function like System.Guid.NewGuid() in .NET (or similar functions in other languages).

Let’s take a look at this by creating a table and populating it:

2017-10-26

I recently posted that I was moving to this new blog and wanted to thank Adam Machanic and Peter DeBetta.

Well I need to thank Adam Machanic even more. I was intending to just start the new blog from scratch and leave the old one in place but Adam was working on code to help to migrate to WordPress-based engines from Community Server, so he offered to let me Alpha test it.

2017-10-20

For many years now, I’ve been hosting my blog at sqlblog.com. I can’t thank my old friends Adam Machanic and Peter DeBetta enough for starting SQL Blog and for providing many of us a home to post about all things SQL. But I know they’ve had challenges (and no doubt costs) from doing so, and the Community Server software is getting older and harder to look after now.

So, for me, it’s time for a new blog. I’m sure it will be quite a challenge to ever get back up to the number of views (and consolidated views that all on SQL Blog benefited from) but it’s time to start.

2017-10-18

Had a client today asking about this situation. They were working away on a machine and suddenly they got the message “Failed to update the database because the database is read-only”.

• The user hadn’t changed anything that they were aware of.
• Based on the user’s permissions (ie: what they could see), everything in SSMS looked normal.
• When they checked the sys.databases view, the database showed MULTI_USER.
• There was enough disk space.
• Folder permissions had not changed.
• The user was puzzled.

The issue was caused by the database being part of an availability group, and the AG had failed over. So suddenly, the user was connected to the replica database, not the primary. This is why the database said it was read-only.

2017-08-12