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?
Contrary to the accepted answer, the uniqueidentifier datatype in SQL Server is indeed a good candidate for a primary clustering key; so long as you keep it sequential.
This is easily accomplished using (newsequentialid()) as the default value for the column.
If you actually read Kimberly Tripp's article you will find that sequentially generated GUIDs are actually a good candidate for primary clustering keys in terms of fragmentation and the only downside is size.
If you have large rows with few indexes, the extra few bytes in a GUID may be negligible. Sure the issue compounds if you have short rows with numerous indexes, but this is something you have to weigh up depending on your own situation.
Using sequential uniqueidentifiers makes a lot of sense when you're going to use merge replication, especially when dealing with identity seeding and the woes that ensue.
Server calss storage isn't cheap, but I'd rather have a database that uses a bit more space than one that screeches to a halt when your automatically assigned identity ranges overlap.