SQL Server GUID

Summary: in this tutorial, you will learn about the SQL Server GUID and how to use the NEWID() function to generate GUID values.

Introduction to SQL Server GUID

All things in our world are numbered e.g., books have ISBNs, cars have VINs, and people have social security numbers (SSN).

The numbers, or identifiers, help us reference things unambiguously. For example, we can identify John Doe by using his unique social security number 123-45-6789.

A globally unique identifier or GUID is a broader version of this type of ID numbers.

A GUID is guaranteed to be unique across tables, databases, and even servers.

In SQL Server, GUID is 16-byte binary data type, which is generated by using the NEWID() function:

SELECT 
    NEWID() AS GUID;
Code language: SQL (Structured Query Language) (sql)

If you execute the above statement several times, you will see different value every time. Here is one of them:

GUID
------------------------------------
3297F0F2-35D3-4231-919D-1CFCF4035975

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

In SQL Server, the UNIQUEIDENTIFIER data type holds GUID values.

The following statements declare a variable with type UNIQUEIDENTIFIER and assign it a GUID value generated by the NEWID() function.

DECLARE 
    @id UNIQUEIDENTIFIER;

SET @id = NEWID();

SELECT 
    @id AS GUID;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

GUID
------------------------------------
69AA3BA5-D51E-465E-8447-ECAA1939739A

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

Using SQL Server GUID as primary key

Sometimes, it prefers using GUID values for the primary key column of a table than using integers.

Using GUID as the primary key of a table brings the following advantages:

• GUID values are globally unique across tables, databases, and even servers. Therefore, it allows you to merge data from different servers with ease.
• GUID values do not expose the information so they are safer to use in public interface such as a URL. For example, if you have the URL https://www.example.com/customer/100/ URL, it is not so difficult to find that there will have customers with id 101, 102, and so on. However, with GUID, it is not possible: https://www.example.com/customer/F4AB02B7-9D55-483D-9081-CC4E3851E851/

Besides these advantages, storing GUID in the primary key column of a table has the following disadvantages:

• GUID values (16 bytes) takes more storage than INT (4 bytes) or even BIGINT(8 bytes)
• GUID values make it difficult to troubleshoot and debug, comparing WHERE id = 100 with WHERE id = 'F4AB02B7-9D55-483D-9081-CC4E3851E851'.

SQL Server GUID example

First, create a new table named customers in the marketing schema:

CREATE SCHEMA marketing;
GO

CREATE TABLE marketing.customers(
    customer_id UNIQUEIDENTIFIER DEFAULT NEWID(),
    first_name NVARCHAR(100) NOT NULL,
    last_name NVARCHAR(100) NOT NULL,
    email VARCHAR(200) NOT NULL
);
GO
Code language: SQL (Structured Query Language) (sql)

Second, insert new rows into the marketing.customers table:

INSERT INTO 
    marketing.customers(first_name, last_name, email)
VALUES
    ('John','Doe','[email protected]'),
    ('Jane','Doe','[email protected]');
Code language: SQL (Structured Query Language) (sql)

Third, query data from the marketing.customers table:

SELECT 
    customer_id, 
    first_name, 
    last_name, 
    email
FROM 
    marketing.customers;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

In this tutorial, you have learned about the SQL Server GUID and how to use the NEWID() function to generate GUID values.