GUID Ordering in SQL Server
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
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
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
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
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
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.