Sequential GUIDs

Today we found a table that was perfect for testing the newsequentialid() default. My colleague killspid thought it might be useful to talk about it and said "Blog it!".

This new feature has been added to SQL Server 2005 although it was available in SQL Server 2000 with the addition of an extended stored procedure written by Gert Drapers.

The Books Online state the following for the newsequentialid function:
"Creates a GUID that is greater than any GUID previously generated by this function on a specified computer." I can hardly say I ever saw a statement more simple but yet so complete.

Anyway, we decided to play around a bit with the sequential GUID too determine if this statement said it all. One of the most important limitations is that it can only be used as a default for a column with the uniqueidentifier type, doing otherwise will result in the following error:
"The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.".

It's a default so can we override the contents? Well it turns out you can but there is nothing sequential about that is there?

But why would you want a GUID to be sequential? Because it makes quite a good clustering key candidate. It's unique, static, relatively narrow and sequential. All these factors make it a nice alternative to an identity for some tables. The good news is that it helped us going from a 2 seconds process to a 500 msecs process and all we had to do is change the newid() default to newsequentialid() and of course change the fillfactor since it would be a waste to keep it low with a sequential key. Should you run off and change all your defaults? No, because as always it all depends!

Here is a little test script that proves a few of these points:


SET NOCOUNT ON
GO
CREATE TABLE myGuidTest
(myGuid uniqueidentifier DEFAULT(NewSequentialID()))
GO
CREATE TABLE myGuidTest2
(myGuid uniqueidentifier DEFAULT(NewSequentialID()))
GO
--Let's see if it really is "greater than any GUID previously generated by this function on a specified computer"
INSERT INTO myGuidTest VALUES (default)
GO 5
INSERT INTO myGuidTest2 VALUES (default)
GO 5
INSERT INTO myGuidTest VALUES (default)
GO 5
SELECT * FROM myGuidTest ORDER BY 1
GO
SELECT * FROM myGuidTest2 ORDER BY 1
GO
--Can we insert our own GUID?
DECLARE @newID uniqueidentifier
SET @newID = NewID()
SELECT @newID
INSERT INTO myGuidTest VALUES (@newID)
GO
INSERT INTO myGuidTest VALUES (default)
GO 5
SELECT * FROM myGuidTest ORDER BY 1
--Cleanup
DROP TABLE myGuidTest
DROP TABLE myGuidTest2