This post originated from an RSS feed registered with .NET Buzz
by Marcus Mac Innes.
Original Post: Guid Primary Keys are Revolutionised in SQL Server 2005
Feed Title: Marcus Mac Innes' Blog
Feed URL: http://www.styledesign.biz/weblogs/macinnesm/Rss.aspx
Feed Description: Issues relating to .NET, Service Oriented Architecture, SQL Server and other technologies.
Jimmy Nilssonblogs about the new NEWSEQUENTIALID() function in SQL Server 2005 (Yukon) which provides an incremental GUID generator and hence the long overdue answer to uniqueidentifier as a primary key. While the importance of this may well be lost on non database oriented people, this function alone will revolutionise our choice of table primary keys.
Traditionally GUID generation produces essentially random numbers which cannot realistically be used as primary keys since new rows are inserted into the table at random positions leading to extremely slow inserts as the table grows to even a moderate size. Inserting into the middle of a table with clustered index, rather than appending to the end can potentially cause the database to have to move large portions of the data to accommodate space for the insert. This can be very slow.
Jimmy wrote an article back in 2002 which provided a reasonable solution to the problem, but unfortunately one that I was not personally a fan of. I preferred to maintain INT primary keys for both space and performance reasons and provided a GUID as a secondary key whenever there was a requirement to publish an identifier outside of a service boundary. I think this also fits with Pat Helland's thoughts on data when he talks about Data on the Outside vs. Data on the Inside because the INT keys are private to the internal workings of the service while the GUID provide the necessary external unique identifiers.
All this (as Jimmy points out) will however be in the past with the introduction of NEWSEQUENTIALID(). Let's hope they provide an equivalent function in the CLR!
Previous Topic
