Using ROWGUIDCOL in SQL Server

Introduction

This artcile explains the use of ROWGUIDCOL in SQL Server.

Background

Normally, when we use UNIQUEIDENTIFIER column for PRIMARY KEY, we assign NEWID() as the DEFAULT value for the column. SQL Server offers a more efficient and convenient way of achieving it.

Using the Code

For example, this is what we would normally use:

//
// Using UNIQUEIDENTIFIER with NEWID()
//
CREATE TABLE MyTable (ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID()...

Instead of the above, we can use the below definition:

//
// Using UNIQUEIDENTIFIER with ROWGUIDCOL and NEWSEQUENTIALID()
//
        
CREATE TABLE MyTable (ID UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY DEFAULT NEWSEQUENTIALID()...

Points of Interest

We have the below advantages with this approach:

1) The UNIQUEIDENTIFIER along with ROWGUIDCOL NEWSEQUENTIALID() is far more efficient than normal UNIQUEIDENTIFIER along with NEWID(). 2) The Unique Ids generated with the second approach are sequential in nature, similar to IDENTITY values. 3) There can be max of one ROWGUIDCOL for a table. 4) You can query the table for ROWGUIDCOL. Example: SELECT ROWGUIDCOL FROM MyTable

History

Keep looking for updates, please rate this tip/trick.