The Bit Bucket

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

Dennes Torres recently wrote a really excellent article introducing Graph Objects in SQL Server 2017. You’ll find it at Simple Talk.

I’ve also recently published a SQL Down Under podcast with Shreya Verma from the SQL Server team, where we discussed Graph extensions to SQL Server. (It’s part of a joint interview. We also discussed Adaptive Query Plans with Joe Sack). You’ll find that here: https://podcast.sqldownunder.com

I loved Dennes’ article but one aspect that I want to talk a little more about is the naming of Edge tables. As I read the article, I was a little troubled about the edge naming. (Let me stress that it’s a very, very minor item in a very good article and just my opinion).

2017-07-10

Hi Folks,

I had the pleasure recently to record a podcast with an old friend Joe Sack who is working with Adaptive Query Plans in SQL Server 2017.

Joining us was Shreya Verma (also from the product team), discussing the Graph API (also in SQL Server 2017).

You’ll find it here: SQL Down Under Podcast

Enjoy !

2017-06-28

Hi Folks,

We’ve just created the May 2017 update for SDU Tools. We’re pretty excited about what we’re delivering this month.

The first part that we’ve added is a set of tools to make it really, really easy to capture a performance query trace, to load it, and to perform a basic analysis of it. There are three tools involved in this:

• CapturePerformanceTuningTrace – Just give the trace a name, say where it should be stored, which databases to filter on (if any), and how long you want the trace to run. We do the rest.
• LoadPerformanceTuningTrace – This tool loads the completed trace into a table. AnalyzePerformanceTuningTrace - And then we find the things in the trace that are of interest – automatically

Next, we’ve added a useful tool to help with your database consistency. I often run into databases where columns that hold the same information are defined differently across the database.

2017-05-07

When applications need to call stored procedures, they try to work out what the returned data will look like ie: which columns come back, what data types are they, etc.

The old way of doing this was to call SET FMTONLY ON. Unfortunately, many applications like Biztalk and versions of Reporting Services still did this, and/or still do this. The same issue happens with LINQ to SQL and other tools that use SQL Metal.

2017-04-11

When you output a series of numbers in T-SQL, people often want to right-align the numbers. So instead of numbers that look like this:

They want an output that looks like this:

Now the first thing to understand is that this is generally a client-tool or program issue, not a T-SQL issue. I ran these queries in SQL Server Management Studio and it could just as easily have decided to show me numbers right-aligned.

2017-04-09

Yesterday, I discussed changing the case of T-SQL strings to ProperCase, TitleCase, SnakeCase, and KebabCase.

But there are other case options that can be needed. For example, often when I’m programmatically generating code, I want to create identifiers for objects. While many people will use SnakeCase for that, in Microsoft-related land (particularly around .NET), it’s common to use both PascalCased and camelCased strings.

In the April2017 update to SDU_Tools, we added functions to do all of the above. SDU Tools is a free library for SQL Server developers and DBAs. You can easily just add it to your database, then call the functions as needed. The functions get added in a separate schema and are easy to remove later if you ever wish to. (We hope you won’t want to). They’re also easy to upgrade.

2017-04-08