Almost a year ago I posted my second note regarding GUIDs and Databases.  That post is available here: http://blogs.interknowlogy.com/billsheldon/archive/2005/07/29/369.aspx but in the time since that post SQL Server has seen a new release and now we have SQL Server 2005 available and the SQL Team has added another wrinkle to the discussion.

In talking to one of my coworkers (Tim McCarthy) the other day he mentioned that he had run into a new SQL Function which would increment GUIDs, which is the reason for this post.  Now to briefly recap my previous posts one of the biggest disadvantages to using GUIDs is that people tend to figure that since they should remain unique they will make a good primary key.  Unfortunately the nature of a Primary Key is that it by default relies on a clustered index.  For those unfamiliar with indexes, a clustered index is an index in which entries are written to the disk in their sorted order so that lookups against it are very fast.  As such the fastest way to add entries to a clustered index is with the use of incremental values so that the system appends new entries to the end of the index.  This reduces the number of page splits which are far more expensive and occur when you insert items into the middle of the index.

Unfortunately by default GUIDs are randomly generated which results in new entries constantly being inserted into the middle of the clustered index.  I knew one company which got a new implementation of their database from an offshore company where all the indexes had been changed to GUIDs and as a result their new database couldn't even support 10% of their customer base.  Their short term solution, outside of rearchitecting the work, was to create a middle tier function which would sequentially generate those GUIDs to cut down on page splits.  (Long term they reduced the number of tables using GUIDs and generally cleaned up a mess of other problems - the GUIDs alone weren't the only issue.) However, if they had had SQL 2005 available then instead of needing several days to write and a custom solution they could simply have implemented the new "NewSequentialID()" function.  NewSequentialID does just what the name implies it creates a new GUID which is one greater then the last GUID created on that server.  Note that it is server sequential and not table sequential, for each table the guarantee is only that each new entry will be larger then the last.

More information on this new function is available at: http://msdn2.microsoft.com/en-us/library/ms189786.aspx

As noted in that function there are a couple of issues - for example using this function on you user table makes it possible to guess the internal identity of the next user, so if you are using the randomness of GUIDs as part of a security scheme that prevents easy guessing of other user id's this function isn't for you.

Additionally it limits one of the other claims for GUIDs which is that you can use them to make data portable between databases.  Since randomly generated GUIDs are (almost always) unique even though generated at the same time on different machines it is possible to transfer all of the data associated with a user's GUID into another database with minimal risk of collision. 

Of course these were the only two functions that made using a GUID a useful idea.  So don't see this as a function which will suddenly allow you to use GUIDs without a performance penalty.  Fact is int and bigint columns with an identity property will still be faster for your overall database.  Instead this function is there for all of you who already have that GUID primary key and now need a quick and easy solution to reduce it's impact on your overall database performance.