MS SQL Server Row GUID Column

Question

I apologize for asking what seems like a tired question, but no forum response or documentation I've read seems to provide an adequate answer.

What is the purpose of the Row GUID Column property in MS SQL Server 2008?

Let me extend on that. I've seen "Don't use GUID as the PK" repeated as the answer for this question, which seems to be completely unrelated to me. If I set Row GUID Column is it forcing that GUID column to be the primary key or something?

To rephrase my original question: If I have a GUID column in my table that does not play any role in indexing, do I stand to gain anything from setting that GUID column as the Row GUID Column?

Solution 1

It enables you to use the $ROWGUID variable instead of the column name. It's used mostly for merge replication. It doesn't force the GUID column for PK, you can only declare PKs explicitly. The answer to your last question is no, unless you plan on implementing Merge Replication.

Solution 2

The ROWGUIDCOLUMN is used primarily in replication scenarios where you need to combine the contents of several satelite databases' tables into one central DB.

Adding a ROWGUIDCOLUMN to the tables being replicated ensures that the replication engine can use the ROWGUIDCOLUMN field to differentiate between records with the same primary key values.

Because this is a feature primarily used to ensure smooth replication, you should avoid using this column as your primary key. Your primary key should be a logical business entity that is under your control and which doesn't affect the operational characteristics of specific features of your chosen DB engine.

Solution 3

RowGUID column is used for replication. It allows the replication engine to merge correctly. When replication is enabled this column will be added to tables that do not already have a RowGUID column.

You will not gain anything by using it unless you are planning to implement replication. It is equivalent to setting newsequentialid() as the default value for a column.

Comments

[instantmusic]

So clear, so concise, thank you everyone >_< I'm thoroughly surprised that I couldn't find an answer to this question elsewhere(rather, all the unrelated discussions seemed to cloud over the valid responses on other forums) Thank you for your help!

[Solomon Rutzky]

Nice. And just FYI, starting in SQL Server 2012, it is also used for FILESTREAM functionality :-).

[June]

Brilliant explanation !

[Solomon Rutzky]

Rich (and @Jeffrey): how does marking a column as ROWGUIDCOL ensure "that the replication engine can use the ROWGUIDCOLUMN field to differentiate between records with the same primary key values" when it does not guarantee uniqueness? Yes, by default the value will nearly always be unique, but nothing prevents manually adding duplicate values, outside of adding a Unique Index/Constraint.

[June]

This is an interesting article blog.sqlauthority.com/2012/06/18/…