Thursday, June 4, 2009

RowGUID

I learned something new today, regarding the RowGUID property in the SSMS Table Design view

In short, to use GUID as Primary Key (not recommended in general), set the column data type to uniqueidentifier, and default value of newid(), or better newsequentialid()

CREATE TABLE Product
ProductID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT (newsequentialid()), 
ProductName VARCHAR(50), 
ListPrice money 
)    


Only set the RowGuid property to Yes if you are using Replication

Source
The setting of RowGuid=No is correct for a GUID pk. The RowGuid is used by SQL Server replication to uniquely identify rows across replicated databases (i.e. you have have two records with the same PK on different servers and when you replicate one database into the other, SQL Server will handle it). So, the RowGuid property of a column is independent from the primary key. Generally, when you use replication and a GUID pk, you can make them the same column.


Another good read - Can Sequential_GUID be the PK?

4 comments:

  1. The setting of RowGuid=yes is correct for a GUID pk.

    ReplyDelete
  2. The RowGUID data type is uniqueidentifier with 'Is RowGuid' value = Yes and 'Default Value' = (newid())

    ReplyDelete
  3. Also it is not 'better' to use newsequentialid at all - you might as well just use an identity column.

    It says the same in the SQL Server documentation.

    ReplyDelete
  4. It seems to me that Jerry Hung ment to say that, newsequentialid() is better than newid() to have pk guid, which is correct since you avoid table splitting problem.

    ReplyDelete