ROWGUIDCOL

I've read everything BOL says about ROWGUIDCOL and it's still not clear to me why it is necessary. It appears to be similar to an IDENTITY property, except that it does not have a seed or an increment. Because of that, it appears to have no practical purpose other than annotating a CREATE TABLE script to let people know it is used to identify a row.

ROWGUIDCOL as described seems to fit the case of where a guid is the primary key (I do realize it does not enforce uniqueness).

IDENTITY can generate unique values for a key for a single table on a single machine. If you want to automatically generate UNIQUE values accross multiple tables or machines, use a uniqueidentifier column that defaults to NEWID() or is always given a new guid value by your application when your application creates a row. ROWGUIDCOL is just a convenient designator for a GUID-based unique ID column. It also gives you the ability to reference it using $ROWGUID. GUID-based identifiers are used extensively in replication scenarios.

|||Thanks for that clarification. $ROWGUID could be useful. What if the guid is only the leading edge of the primary key, not the entire primary key? In other words, does the guid have to uniquely identify the row for $ROWGUID to work?

NEWSEQUENTIALID() is an alternative to NEWID(). As I posted in http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=52136, Newid in C# caused reduced performance because of index page splits. Using NEWID() in T-SQL has the same problem. Using NEWSEQUENTIALID() instead of NEWID() greatly improved insert performance without any adverse effects on select performance. NEWSEQUENTIALID() provides an ascending sort order to the guids, which makes it more similar to IDENTITY than NEWID() is.sql