Guid in SQL Server

Suggested Videos
Part 133 - sp_depends in SQL Server
Part 134 - Sequence object in SQL Server 2012
Part 135 - Difference between sequence and identity in SQL Server

In this video we will discuss
1. What is a GUID in SQL Server
2. When to use GUID
3. Advantages and disadvantages of using a GUID

What is in SQL Server
The GUID data type is a 16 byte binary data type that is globally unique. GUID stands for Global Unique Identifier. The terms GUID and UNIQUEIDENTIFIER are used interchangeably.



To declare a GUID variable, we use the keyword UNIQUEIDENTIFIER

Declare @ID UNIQUEIDENTIFIER

SELECT @ID = NEWID()

SELECT @ID as MYGUID

How to create a GUID in sql server
To create a GUID in SQL Server use NEWID() function

For example, SELECT NEWID(), creates a GUID that is guaranteed to be unique across tables, databases, and servers. Every time you execute SELECT NEWID() query, you get a GUID that is unique.

Example GUID : 0BB83607-00D7-4B2C-8695-32AD3812B6F4

When to use GUID data type : Let us understand when to use a GUID in SQL Server with an example. 

1. Let us say our company does business in 2 countries - USA and India. 

2. USA customers are stored in a table called USACustomers in a database called USADB.

Create Database USADB

Go

Use USADB

Go

Create Table USACustomers

(

     ID int primary key identity,

     Name nvarchar(50)

)

Go

Insert Into USACustomers Values ('Tom')

Insert Into USACustomers Values ('Mike')

Select * From USADB.dbo.USACustomers

The above query produces the following data


3. India customers are stored in a table called IndiaCustomers in a database called IndiaDB.

Create Database USADB

Go

Use USADB

Go

Create Table USACustomers

(

     ID int primary key identity,

     Name nvarchar(50)

)

Go

Insert Into USACustomers Values ('Tom')

Insert Into USACustomers Values ('Mike')

Select * From USADB.dbo.USACustomers

The above query produces the following data


In both the tables, the ID column data type is integer. It is also the primary key column which ensures the ID column across every row is unique in that table. We also have turned on the identity property,

4. Now, we want to load the customers from both countries (India & USA) in to a single existing table Customers.

First let's create the table. Use the following SQL script to create the table. ID column is the primary key of the table.

Create Table Customers

(

     ID int primary key,

     Name nvarchar(50)

)

Go

Now execute the following script which selects the data from IndiaCustomers and USACustomers tables and inserts into Customers table

Insert Into Customers

Select * from IndiaDB.dbo.IndiaCustomers

Union All

Select * from USADB.dbo.USACustomers

We get the following error. This is because in both the tables, Identity column data type is integer. Integer is great for identity as long as you only want to maintain the uniqueness across just that one table. However, between IndiaCustomers and USACustomers tables, the ID coulumn values are not unique. So when we load the data into Customers table, we get "Violation of PRIMARY KEY constraint" error.

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object 'dbo.Customers'. The duplicate key value is (1).
The statement has been terminated.

A GUID on the other hand is unique across tables, databases, and servers. A GUID is guaranteed to be globally unique. Let us see if we can solve the above problem using a GUID.

Create USACustomers1 table and populate it with data. Notice ID column datatype is uniqueidentifier. To auto-generate the GUID values we are using a Default constraint.

Use USADB

Go

Create Table USACustomers1

(

     ID uniqueidentifier primary key default NEWID(),

     Name nvarchar(50)

)

Go

Insert Into USACustomers1 Values (Default, 'Tom')

Insert Into USACustomers1 Values (Default, 'Mike')

Next, create IndiaCustomers1 table and populate it with data.

Use IndiaDB

Go

Create Table IndiaCustomers1

(

     ID uniqueidentifier primary key default NEWID(),

     Name nvarchar(50)

)

Go

Insert Into IndiaCustomers1 Values (Default, 'John')

Insert Into IndiaCustomers1 Values (Default, 'Ben')

Select data from both the tables (USACustomers1 & IndiaCustomers1). Notice the ID column values. They are unique across both the tables.

Select * From IndiaDB.dbo.IndiaCustomers1

UNION ALL

Select * From USADB.dbo.USACustomers1

Now, we want to load the customers from USACustomers1 and IndiaCustomers1 tables in to a single existing table called Customers1. Let us first create Customers1 table. The ID column in Customers1 table is uniqueidentifier.

Create Table Customers1

(

     ID uniqueidentifier primary key,

     Name nvarchar(50)

)

Go

Finally, execute the following insert script. Notice the script executes successfully without any errors and the data is loaded into Customers1 table.

Insert Into Customers1

Select * from IndiaDB.dbo.IndiaCustomers1

Union All

Select * from USADB.dbo.USACustomers1

The main advantage of using a GUID is that it is unique across tables, databases and servers. It is extremely useful if you're consolidating records from multiple SQL Servers into a single table. 

The main disadvantage of using a GUID as a key is that it is 16 bytes in size. It is one of the largest datatypes in SQL Server. An integer on the other hand is 4 bytes,

An Index built on a GUID is larger and slower than an index built on integer column. In addition a GUID is hard to read compared to int.

So in summary, use a GUID when you really need a globally unique identifier. In all other cases it is better to use an INT data type.