Update: Added Link fro Sql Server Magazine.
Since 2001 I have advocated Guids as surrogate primary keys in database tables over identity fields. The reasons are two fold. First, I can design for "one way traffic" in that I don't need to query the identity field and wire it back in saving a lot of goo code. Second, I can wire up an object graph with persistent ids and save them in one go, saving significant round trip costs. A third side benefit is that any data error in the object graph can be tagged with the Guid so that any client can navigate to the offending object. All in all, an ORM-geek's dream.
The catch is clustered key index fragmentation. I'll point you shortly to blog post by Kimberly Tripp that explains the issue. Suffice it to say that Guid primary keys it has been a hard sell to DBAs (Having a surrogate key itself can be a hard sell). However, I believe that "Code for Clarity. Optimize Empirically" applies to databases as well. A clear data model makes everyone's life easier. So what to do?
Well, SQL Server 2005 now supports the newsequentialid function. Defined only for the default clause, it creates a unique monotonically increasing uniqeidentifer field. Kimberly Tripp gives a thumbs-up along with some great analysis. A must read.
Now the issue, of course, is that I want to create Guids in the Id properties of the objects. I can think of two steps to enable compatibility. The first step is a configurablity switch so that some model objects do not create their Guids. You would need to inactivate the objects after they are persisted or implement round-tripping the keys, which is untenable for object graph persistence. However if you need the performance you already are special-casing your code, so some extra thought for these objects should not be a real issue.
Paul Wilson asked:
I've always been under the impression that the main reason to choose guids over int (or bigint) identities was for either replication and/or so clients could generate their own unique keys. But it doesn't seem like this new sequential "guid" can be of any value in either of these situations -- so why not just use an identity?
For me the issue is object graphs. To save an object graph performantly you need to put all the inserts/updates/deletes in one batch. When you do so, you need some prewiring of the objects, since, on insert their persistant ids (if you are not using identities) would not exist yet. Even if you did some sort of use of @@identity in the batch you still have the problem of handling errors in the batch. If you have an error persisting change in an object in an object graph (or a collection of objects, for that matter) how do you communicate which object erred? You need a prewired id. Having a Guid solves all these problems.
Moreover, this benefit reaches the whole way up and down the call stack. If you have a JScript Ajax client call a web service call an other web service call the database, you need to propagate the ids and/or errors the whole way up the call chain. This either produces a lot of cumbersome coding or painful architectonic. The Guid, however is painless: anyone can generate a Guid, so objects can be created anywhere along the chain and anyone who needs a persistent handle to the object a priori gets one for free.
Thus the question remains: how do you integrate a frame work that depends on early generation of Guids with newsequentailid, which requires late generation of Guids. That was what I was addressing in this post.
Oh, a final benefit is it's easy to encrypt a Guid; some ints are rather small for encrypting, in my opinion. An encrypted Guid makes a nice opaque id in a URL. Remember, don't forget the salt!
More on newsequentialid from SQL Server Magazine (subscription required)