What is the purpose of a Row_GUID column?

I've been digging around in the AdventureWorks2012 database and see Row_GUID used in several tables.

There are 2 parts to my question:

When should I include a Row_GUID column?

What are the uses and benefits of a Row_GUID column?

ROWGUIDCOL is primarily used for MERGE replication, and is also required for FILESTREAM, but can be used in any scenario where you want an immutable column separate from the primary key (e.g. in the case where a primary key value can change, but you still want to be able to tell which row was which before and after the change).

USE tempdb;
GO

CREATE TABLE dbo.example
(
  name sysname PRIMARY KEY, 
  rowguid uniqueidentifier NOT NULL DEFAULT NEWID() ROWGUIDCOL
);

INSERT dbo.example(name) VALUES(N'bob'),(N'frank');

SELECT * FROM dbo.example;

UPDATE dbo.example SET name = N'pat' WHERE name = N'bob';
UPDATE dbo.example SET name = N'bob' WHERE name = N'frank';

SELECT * FROM dbo.example;

DROP TABLE dbo.example;

Now, if replication, or your application, or what have you is paying attention, it will notice that:



See here, here, and the "Snapshot Considerations" section here for more info.

StackExchange Database Administrators Q#140558, answer score: 31