SQL UNIQUEIDENTIFIER Data Type

* GUID = Globally Unique Identifier

A table with a UNIQUEIDENTIFIER column.

CREATE TABLE DemoTable  
( 
  Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
  FullName VARCHAR(100)
);
GO  

INSERT INTO DemoTable VALUES ('Harold Smith');  
INSERT INTO DemoTable VALUES ('Robert Johnson');  
INSERT INTO DemoTable VALUES ('Janice Lopez');
INSERT INTO DemoTable VALUES ('Kelly Wilson'); 
INSERT INTO DemoTable VALUES ('Grace Taylor'); 
GO  

SELECT * FROM DemoTable;
GO

DROP TABLE DemoTable;
GO

As you can see, UNIQUEIDENTIFIER values are alphanumeric and much longer than simple INTEGER values.

Why use UNIQUEIDENTIFIER over IDENTITY?

First of all, IDENTITY primary keys are perfect for standalone databases. They are fast, efficient, and each table row gets a unique identifier without much effort.

Now suppose that we have two databases, US-Customer and EURO-Customer, that need to be merged. Both have Customer tables with IDENTITY primary keys. The problem is that there will be duplicate key values because each one started (most likely) with 1,1 for seed and increment values. This is where UNIQUEIDENTIFIER comes in.

If the two databases were using UNIQUEIDENTIFIER primary keys, then the merge would be simple. The primary keys are globally unique so there wouldn't be any duplicates.

Each type has its own advantages and disadvanteges. IDENTITY is lightweight, simple, and fast. UNIQUEIDENTIFIER takes up more space, indexes are larger, and as a DBA it is easier to talk about IDENTITY values (for example record 334900), rather than UNIQUEIDENTIFIER values (for example, record E9C15E05-76A6-4986-B02E-99C3E5EFFC7D).

As a general rule of thumb, IDENTITY works well in most cases. But for large, distributed scenarios with multiple databases, UNIQUEIDENTIFIER may be your best choice.

Let's run the above query again and confirm that the NEWID function generates random GUID values that are impossible to guess.

CREATE TABLE DemoTable  
( 
  Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
  FullName VARCHAR(100)
);
GO  

INSERT INTO DemoTable VALUES ('Harold Smith');  
INSERT INTO DemoTable VALUES ('Robert Johnson');  
INSERT INTO DemoTable VALUES ('Janice Lopez');
INSERT INTO DemoTable VALUES ('Kelly Wilson'); 
INSERT INTO DemoTable VALUES ('Grace Taylor'); 
GO  

SELECT * FROM DemoTable;
GO

DROP TABLE DemoTable;
GO

Indeed, the GUID values are all over the place and cannot be guessed.

Next, we'll use NEWSEQUENTIALID to generate the GUID values.
You will see that the values are sequential and it is easy to guess the next value.

CREATE TABLE DemoTable  
( 
  Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
  FullName VARCHAR(100)
);
GO  

INSERT INTO DemoTable VALUES ('Harold Smith');  
INSERT INTO DemoTable VALUES ('Robert Johnson');  
INSERT INTO DemoTable VALUES ('Janice Lopez');
INSERT INTO DemoTable VALUES ('Kelly Wilson'); 
INSERT INTO DemoTable VALUES ('Grace Taylor'); 
GO  

SELECT * FROM DemoTable;
GO

DROP TABLE DemoTable;
GO

In this example only the second digit of the GUID values change with each row.
We can easily guess the next value: 568881A8-C540-EC11-94CF-B46921AB1516.

There are scenarios in which incrementing GUID values are useful. If that is the case, you use NEWSEQUENTIALID.

In addition, NEWSEQUENTIALID is more efficient than NEWID both in performance and storage-wise.

However, for security and other reasons it is generally preferred to use NEWID.

• Our Sql NCHAR Data Type Reference
• Our Sql VARCHAR Data Type Reference
• Our Sql Data Types Reference