SQL UNIQUEIDENTIFIER Data Type

The UNIQUEIDENTIFIER data type is a 16-byte GUID*.

This data type is used as primary key alternative to IDENTITY columns.

UNIQUEIDENTIFIER is globally unique, whereas IDENTITY is unique within a table.

UNIQUEIDENTIFIER values can be generated with NEWID or NEWSEQUENTIALID functions.

* GUID = Globally Unique Identifier

Example

#

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

The primary key in this table is of type UNIQUEIDENTIFIER.

The values are generated by the NEWID function.

With this setup, the primary key values are autogenerated, just like IDENTITY.

Result:  5 records
Id FullName
7FA7C1E5-D9B5-44B0-8556-5AA5B0701E4F Harold Smith
026F4401-3829-4245-B540-85FA2254A427 Robert Johnson
59870ABF-518F-49C6-BEFC-4E2357946EE3 Janice Lopez
8B1644DB-C495-47B2-AC8A-5C4858AB6B38 Kelly Wilson
00C616FE-F98D-420F-B6A3-BD47755283BF Grace Taylor

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


Using UNIQUEIDENTIFIER

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.


NEWID

#

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
Result:  5 records
Id FullName
32B0591B-947B-4632-9219-095D51756222 Harold Smith
BE75FA3B-62C0-4EF8-B07C-1B476C291493 Robert Johnson
6D7BCC5F-676D-43E1-97BB-BD964CC38DBC Janice Lopez
92888E23-D699-488D-B89A-CA523AC85474 Kelly Wilson
07663A72-D44D-49E2-8EF3-EC6ECA8EA7E5 Grace Taylor

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


NEWSEQUENTIALID

#

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
Result:  5 records
Id FullName
518881A8-C540-EC11-94CF-B46921AB1516 Harold Smith
528881A8-C540-EC11-94CF-B46921AB1516 Robert Johnson
538881A8-C540-EC11-94CF-B46921AB1516 Janice Lopez
548881A8-C540-EC11-94CF-B46921AB1516 Kelly Wilson
558881A8-C540-EC11-94CF-B46921AB1516 Grace Taylor

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.


Compare NEWID vs NEWSEQUENTIALID

#

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.


You may also like



Guides


vsn 3.1