GUID Ordering in SQL Server

Published 25 May 09 11:13 AM | MartinBell

There has been a few postings on the newsgroups and forums recently about using GUIDs as primary keys. When the NEWSEQUENTIALID() function was released with SQL Server 2005, it was seen as a solution to the page split problem that random guids generated by NEWID(). This is fine if you are generating the guid on the database server, but if you generate the guid on the client, what do you do? According the SQL Programmability & API Development Team Blog NEWSEQUENTIALID() is a wrapper to the UuidCreateSequential function, but this is not actually entirely the case as SQL Server orders the guids differently, as described here.

I thought I would write a small test harness that would show this issue. The test harness expects a GUIDTEST database containing a GuidTable to exists. The following SQL will create this database and table:

CREATE DATABASE GUIDTEST ;
GO

USE [GUIDTEST]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GuidTable]') AND type in (N'U'))
      DROP TABLE [dbo].[GuidTable]
GO

CREATE TABLE GuidTable ( id int not null identity,
                         newidguid uniqueidentifier not null default NEWID(),
                        
newsequentualidguid uniqueidentifier not null default NEWSEQUENTIALID(),
                         dotnetguid uniqueidentifier not null,
                         dotnetsequentialguid uniqueidentifier not null,
                         dotnetmodifiedsequentialguid uniqueidentifier not null );
GO

ALTER TABLE GuidTable ADD CONSTRAINT PK_GuidTable PRIMARY KEY CLUSTERED (id) ;
GO

The test harness will populate the GuidTable with random guids using the NEWID() function and Guid.NewGuid(), sequential guids using NEWSEQUENTIALID() and UuidCreateSequential and also sequential guids created with UuidCreateSequential that have the byte order modified to be the same SQL Server.

Once the table has been populated you can use a simple select statement to determine whether the order is correct or not i.e

Guids generated by NEWID()

SELECT * FROM GuidTable
ORDER BY newidguid;
GO

Order by NEWID() generated guid

As you can see, this will not return the results in the same order as which they were inserted.

GUIDs generated by NEWSEQUENTIALID()

SELECT * FROM GuidTable
ORDER BY newsequentualidguid;
GO

Order by NEWSEQUENTIALID() generated guid
   

As expected, the order which the rows are returned is the order the data was inserted.

Random GUIDs generated in .NET

SELECT * FROM GuidTable
ORDER BY dotnetguid;
GO

Order by .NET generated guid

Again, the order using the random guid is not the same as the order which the data was inserted.

GUIDs generated in .NET with UuidCreateSequential
 
SELECT * FROM GuidTable
ORDER BY dotnetsequentialguid;
GO

Order by .NET generated sequential guid

Even though this is a .NET sequential guid, the order returned is not the same as the order which the data was inserted.

Modified GUIDs generated in .NET with UuidCreateSequential

SELECT * FROM GuidTable
ORDER BY dotnetmodifiedsequentialguid;
GO

Order by modified .NET generated sequential guid

This time the order is the same as the order which the data was entered.

From this you can see that if you rely on the order of the guids generated, you can not use UuidCreateSequential without changing the byte order.

When comparing guids in ADO.NET you should also be aware that the behaviour of the SqlGuid type is different to the Guid type. http://msdn.microsoft.com/en-us/library/ms254976(VS.100).aspx, also SQL Server will  return a string representation of a uniqueidentifier in uppercase, but the ToString() functions of the guid and SqlGuid types will always return lowercase strings, which may cause issues when case sensitivity is require (such as XML).

Using the test harness I will also look at how the different methods of guid creation will affect fragmentation.

Comments

# Martin Bell UK SQL Server MVP said on May 25, 2009 02:40 PM:

With the advent of NEWSEQUENTIALID() fragmentation was reducded in clustered indexes, but what if you want to generate guids on the client? I look at the implications on fragmentation for different methods of guid generation

# Dew Drop - May 26, 2009 | Alvin Ashcraft's Morning Dew said on May 26, 2009 01:05 PM:

Pingback from  Dew Drop - May 26, 2009 | Alvin Ashcraft's Morning Dew

# Links for the Week | Jeremiah Peschka, SQL Server Developer said on May 29, 2009 12:20 PM:

Pingback from  Links for the Week | Jeremiah Peschka, SQL Server Developer

This Blog

SQL Blogs

Syndication