Using the ROWGUIDCOL Attribute Flag

ROWGUIDCOL is a metadata marker applied to a single UNIQUEIDENTIFIER column per table, shown in SQL Server Management Studio's table designer as the "Is RowGuid" property. Unlike IDENTITY on an integer column, setting ROWGUIDCOL doesn't generate GUID values on its own, and it doesn't enforce uniqueness either; it only designates which UNIQUEIDENTIFIER column is the table's canonical row identifier, so SQL Server's own subsystems and generic T-SQL scripts know which column to target. Actually generating values, and guaranteeing they're unique, still requires attaching a DEFAULT (NEWSEQUENTIALID()) or DEFAULT (NEWID()) constraint and a UNIQUE or PRIMARY KEY constraint of your own, the same as any other column.

A few rules apply no matter how the column is used: ROWGUIDCOL works only on a UNIQUEIDENTIFIER column, and only one such column per table can carry the flag.

GUID stands for globally unique identifier, a 128-bit value generated by NEWID() or NEWSEQUENTIALID(). The pool of possible values is large enough that a collision between two independently generated GUIDs, anywhere, on any computer, is considered practically impossible, which is exactly why GUIDs are useful for merging data from independent databases without key collisions, a scenario a future course in this series covers in more depth.

That near-certainty of uniqueness is a property of the generation algorithm itself, though, not something the ROWGUIDCOL flag confers on its own. ROWGUIDCOL only marks which column plays that role for a table; it's still a UNIQUE or PRIMARY KEY constraint doing the actual, SQL-Server-enforced uniqueness checking on every insert, the same distinction the previous lesson drew for IDENTITY: a value-generation attribute is not, by itself, a uniqueness guarantee.

Scenario	Use ROWGUIDCOL?	Why
FILESTREAM storage	Mandatory	SQL Server requires a NOT NULL UNIQUE column marked ROWGUIDCOL to map file system streams back to rows
Merge replication	Mandatory	Tracks row changes across publishers and subscribers; SQL Server auto-creates a rowguid column if none exists
Transactional replication (updatable subscriptions)	Mandatory	Relied on to detect conflicting changes made at different nodes
Schema-agnostic queries ($ROWGUID)	Recommended	Lets generic scripts select a table's GUID column without knowing its actual name
An ordinary UNIQUEIDENTIFIER primary or foreign key	Optional	Adds no functional or performance difference outside the scenarios above

Outside those specific cases, a UNIQUEIDENTIFIER column works exactly the same whether or not it's marked ROWGUIDCOL. The flag is worth adding when something else, SQL Server itself, a replication topology, or a generic script, actually needs to find that column without being told its name; it's not a general-purpose "make this more unique" switch.

A table storing unstructured binary data (PDFs, images, archives) on the file system through a VARBINARY(MAX) FILESTREAM column is required to also carry a UNIQUEIDENTIFIER NOT NULL column marked ROWGUIDCOL with a UNIQUE or PRIMARY KEY constraint; SQL Server enforces this at the point the table is created, and will refuse the CREATE TABLE statement without it.

CREATE TABLE Production.DocumentRepository
(
    DocumentID INT IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_DocumentRepository PRIMARY KEY CLUSTERED,

    StreamGuid UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL
        CONSTRAINT UQ_DocumentRepository_StreamGuid UNIQUE NONCLUSTERED
        CONSTRAINT DF_DocumentRepository_StreamGuid DEFAULT (NEWSEQUENTIALID()),

    FileName NVARCHAR(255) NOT NULL,
    FileBlob VARBINARY(MAX) FILESTREAM NULL
);

Here, DocumentID remains the table's ordinary identity primary key, while StreamGuid is the separate, mandatory column that lets SQL Server map each row to its corresponding file on the file system.

When a table is added as an article in merge replication, SQL Server checks for a column already marked ROWGUIDCOL. If one doesn't exist, it automatically alters the table to add a rowguid UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL DEFAULT (NEWSEQUENTIALID()) column on your behalf, whether you planned for it or not. Marking a column yourself ahead of time, if the table already has a natural UNIQUEIDENTIFIER key, avoids replication injecting a redundant second one later.

Transactional replication's updatable subscriptions, where more than one node can make changes rather than one publisher simply pushing changes downstream, rely on a ROWGUIDCOL column in a similar way, to detect when the same row has been modified at two different nodes before those changes are reconciled. In both cases, the column exists to give replication a stable, globally unique way to refer to "this exact row," something an ordinary identity column, unique only within its own table, can't provide across independent databases.

Pre-staging the column yourself, before a table is ever added to a replication topology, is generally worth the small amount of extra planning. It gives you control over the column's name, its datatype precision, and which constraint enforces its uniqueness, rather than accepting whatever replication's own defaults happen to generate; it also avoids the brief schema-change operation replication performs the first time it discovers a table without one, which on a very large, actively used table is worth avoiding outside a planned maintenance window.

Just as $IDENTITY lets a query reference a table's identity column without knowing its name, marking a column ROWGUIDCOL enables the $ROWGUID pseudocolumn in SELECT and WHERE clauses, useful for auditing or synchronization scripts meant to run against many tables with differently named GUID columns (OrderGuid, CustomerGuid, StreamGuid, and so on):

SELECT $IDENTITY AS IdentityValue,
       $ROWGUID  AS RowGuidValue,
       FileName
FROM Production.DocumentRepository
WHERE $ROWGUID = 'A2E5B1C0-8834-4F11-9B12-00155D012304';

The statement above never names DocumentID or StreamGuid directly, which is exactly the point: the same query would run unmodified against any other table that has an identity column and a ROWGUIDCOL column, regardless of what either one happens to be called.

Choosing between a UNIQUEIDENTIFIER column and an integer identity column for a table's primary key comes down to where uniqueness actually needs to hold. An identity column only has to be unique within its own table, generated by a single server, in a single, known order; that's exactly why it's compact (as little as 4 bytes for an INT) and efficient as a clustered index key. A GUID has to be unique across every table, every database, and every server that might ever generate one, independently and without coordinating with anything else, which is a considerably harder problem to solve, and the 16-byte value and weaker index-ordering behavior described above are the price of solving it.

In practice, that makes a GUID primary key worth reaching for specifically when rows genuinely need to merge across independent systems with no shared identity authority: offline mobile clients generating records before they ever reach a central server, or databases from two separate companies being combined after an acquisition. For an ordinary table that only ever lives in one database, an identity column almost always remains the better default.

The ROWGUIDCOL column doesn't have to be a table's primary key, and the two examples in this lesson show both arrangements. A simple table with no other natural key can make its UNIQUEIDENTIFIER column the primary key directly:

CREATE TABLE dbo.Example
(
    RowGuid  UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL
        CONSTRAINT PK_Example_RowGuid PRIMARY KEY
        CONSTRAINT DF_Example_RowGuid DEFAULT (NEWSEQUENTIALID()),
    SomeData NVARCHAR(100) NOT NULL
);

The Production.DocumentRepository table shown earlier in this lesson takes the other approach: DocumentID stays the primary key, an ordinary, compact identity column, while StreamGuid is a separate column that exists purely to satisfy FILESTREAM's requirement. Either design is valid; the right choice depends on whether the table already has, or wants, a more compact key for its own ordinary queries and joins, with the GUID column serving a narrower, specific purpose alongside it.

Because ROWGUIDCOL only marks a column rather than configuring it, it's easy to add the flag and assume the rest follows automatically. It doesn't, and the two most common gaps surface in different ways.

Without a DEFAULT, a NOT NULL ROWGUIDCOL column behaves exactly like any other NOT NULL column with no default: every INSERT that omits it fails, since there's nothing for SQL Server to fall back to.

-- fails if RowGuid has no DEFAULT:
-- Cannot insert the value NULL into column 'RowGuid'...
INSERT INTO dbo.Example (SomeData) VALUES ('test');

Without a UNIQUE or PRIMARY KEY constraint, nothing stops two rows from sharing the same value, since ROWGUIDCOL itself performs no uniqueness check of its own. In practice, two independently generated GUIDs colliding is vanishingly unlikely, but an explicit value supplied by a caller, during a data migration, say, can duplicate one that already exists in the table with nothing in SQL Server to catch it. Pairing the column with a constraint, not just a default, is what actually closes that gap.

Only one UNIQUEIDENTIFIER column per table can carry ROWGUIDCOL at a time. It can be toggled on an existing column without rebuilding the table:

ALTER TABLE Sales.Orders
ALTER COLUMN TrackingGuid ADD ROWGUIDCOL;

ALTER TABLE Sales.Orders
ALTER COLUMN TrackingGuid DROP ROWGUIDCOL;

To audit an existing database for which columns already carry the flag, rather than hunting through table designers one table at a time, query sys.columns.is_rowguidcol directly:

SELECT SCHEMA_NAME(t.schema_id) AS SchemaName,
       t.name                   AS TableName,
       c.name                   AS RowGuidColumnName
FROM sys.tables AS t
INNER JOIN sys.columns AS c
    ON t.object_id = c.object_id
WHERE c.is_rowguidcol = 1;

The same information is available column by column, rather than database-wide, through the older COLUMNPROPERTY and OBJECTPROPERTY functions, which some existing scripts may already rely on:

SELECT COLUMNPROPERTY(OBJECT_ID('Production.DocumentRepository'), 'StreamGuid', 'IsRowGuidCol') AS IsRowGuid,
       OBJECTPROPERTY(OBJECT_ID('Production.DocumentRepository'), 'TableHasRowGuidCol') AS TableHasRowGuid;

Either approach answers the same question; querying sys.columns directly is generally more convenient when auditing many tables at once, while the property functions are handy when a script already has a specific table and column name in hand.

A UNIQUEIDENTIFIER column takes 16 bytes per row, regardless of what it holds, considerably more than a 4-byte INT identity column. On a table with a million rows, that's roughly 16 megabytes for that column alone, on top of everything else stored in the row. If a column doesn't genuinely need to be unique across independent databases, an ordinary INT or BIGINT identity column is the cheaper choice, both in raw storage and in typical index performance, since a 4-byte key fits more rows per index page than a 16-byte one.

Both functions generate a GUID, but they differ in an important way for a column that's part of a clustered index. NEWID() produces values in a random order, while NEWSEQUENTIALID() produces values that increase sequentially until the next server restart. New rows generated with NEWSEQUENTIALID() get added at the end of the index rather than scattered randomly throughout it, which avoids the page-splitting and fragmentation that random NEWID() values tend to cause over time, the same clustered-index concern raised with identity columns in the previous lesson. NEWID() remains the right choice when a value needs to be unpredictable, in a security token, for instance, rather than merely unique.

Marking a column ROWGUIDCOL doesn't, by itself, make SQL Server populate it on every insert; that still requires an explicit default, exactly as covered in the previous lesson for ordinary defaults. The following table generates a GUID automatically whenever an insert leaves TransactionGuid unspecified:

CREATE TABLE dbo.Finance
(
    TransactionGuid UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL
        CONSTRAINT UQ_Finance_TransactionGuid UNIQUE
        CONSTRAINT DF_Finance_TransactionGuid DEFAULT (NEWID()),
    TransactionAmount MONEY NOT NULL
);

An explicit value can still be supplied if one's needed, say, when migrating transactions that already carry a GUID from another system, and an existing row's GUID can be replaced the same way any other column is updated:

UPDATE dbo.Finance
SET TransactionGuid = NEWID()
WHERE TransactionAmount = 500;

In the next lesson, you'll learn how to create and use your own user-defined data types.