Is it a good idea to use rowguid as unique key in database design?
SQL Server provides the type [rowguid]. I like to use this as unique primary key, to identify a row for update. The benefit shows up if you dump the table and reload it, no mess with SerialNo (identity) columns.
In the special case of distributed databases like offline copies on notebooks or something like that, nothing else works.
What do you think? Too much overhead?
Answer
As a primary key in the logical sense (uniquely identifying your rows) - yes, absolutely, makes total sense.
BUT: in SQL Server, the primary key is by default also the clustering key on your table, and using a ROWGUID as the clustering key is a really really bad idea. See Kimberly Tripp's excellent GUIDs as a PRIMARY and/or the clustering key article for in-depth reasons why not to use GUIDs for clustering.
Since the GUID is by definition random, you'll have a horrible index fragmentation and thus really really bad performance on insert, update, delete and select statements.
Also, since the clustering key is being added to each and every field of each and every non-clustered index on your table, you're wasting a lot of space - both on disk as well as in server RAM - when using 16-byte GUID vs. 4-byte INT.
So: yes, as a primary key, a ROWGUID has its merits - but if you do use it, definitely avoid using that column as your clustering key in the table! Use a INT IDENTITY() or something similar for that.
For a clustering key, ideally you should look for four features:
- stable (never changing)
- unique
- as small as possible
- ever-increasing
INT IDENTITY() ideally suits that need. And yes - the clustering key must be unique since it's used to physically locate a row in the table - if you pick a column that can't be guaranteed to be unique, SQL Server will actually add a four-byte uniqueifier to your clustering key - again, not something you want to have....
Check out The Clustered Index Debate Continues - another wonderful and insightful article by Kim Tripp (the "Queen of SQL Server Indexing") in which she explains all these requirements very nicely and thoroughly.
MArc