What is a GUID in SQL Server?

Globally Unique Identifiers, or GUIDs, are 16-byte (128-bit) binary data types in SQL Server that contain values and are represented by unique identifiers. Each table modeling procedure starts with a business key, surrogate key, identity key, etc. An increasingly distinct identity is required when common business entities need to be joined for data storage and analytics.

GUIDs are utilized in these situations since they are distinct for all servers, databases, and tables. Furthermore, when compared to other data formats, GUIDs might be somewhat large.

Using SQL Server GUID as the Primary Key Using NEWID()

You can use the NEWID() function to create a new GUID. It generates a random value as GUID. The goal of GUID design is to be globally unique at any point in time. As discussed above GUID datatype is represented by uniqueidentifier.

A primary key is a unique identifier for every entry in a table that prevents duplicate values from occurring. So, Instead of using integers as the primary key, you can use GUIDs as the PRIMARY KEY Column of a table which brings in some advantages.

SQL Server GUID Example

To execute the query first open SQL Server Management Studio (SSMS) and connect your database engine to the SQL server.

Let’s, create a table with the GUID column as the primary key:
(For eg: My table name is Product2)

Create table

CREATE TABLE Product2 (
    ID uniqueidentifier PRIMARY KEY DEFAULT NEWID(),
    ProductName varchar(255)
);

--Insert records
INSERT INTO  Product2 (ProductName) VALUES ('Oreo');
INSERT INTO Product2 (ProductName) VALUES ('RENEE');

Output:

ID column generates random value using NEWID() (GUID).

Use of NEWSEQUENTIALID() to Generate GUID

NEWSEQUENTIALID() produces a GUID that is larger than any GUID the method has previously produced on that computing device. In some conditions, this mechanism is useful for the reduction of fragmentation.

Let’s create a table with GUID column:

The name of the table is Product3, and the query for it is shown below:

Create table

CREATE TABLE Product3 (
    ID uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    ProductName varchar(255)
);

--Insert records
INSERT INTO  Product3 (ProductName) VALUES ('Oreo');
INSERT INTO Product3 (ProductName) VALUES ('RENEE');

Output:

ID column generates random value using NEWSEQUENTIALID() (GUID).

Explicitly Assign Value

You can explicitly assign value i.e. GUID when inserting the records in the table.
Insert Into Table_Name (Column1,Column2) Values (value1,value2)

For Example:

INSERT INTO Product (ID, ProductName) VALUES ('E2C74A6E-52CC-4BCC-A00A-62B7A7D6D234', 'Custom GUID Record');

Output:

Explicitly assign GUID

Advantages of Guid in SQL Server

• It helps to keep the information safe from public interfaces such as URLs. For instance, it is easy to discover that there will be authors with IDs 101, 102, and so on if you have the URL https://www.example.com/author/100/. However, that is not feasible with GUID, https://www.example.com/author/E4CD02B7-5D56-403D-9041-CC4F3851E751/.
• As it is globally unique it allows to merge data from different servers with ease.

Disadvantages of Guid in SQL Server

• As GUIDs are 16 bytes it takes more storage as compared to INT which is 4 bytes and even BIGINT which is 8 bytes.
• Compared to WHERE id = 101 and WHERE id = ‘E4CD02B7-5D56-403D-9041-CC4F3851E751’, GUIDs make it more difficult to retrieve the data with ease.
• Indexing and Fragmentation: Non-sequential nature can lead to index fragmentation.
• Not human-readable, making debugging more challenging.
• Slower insert performance compared to sequential integers.

Conclusion

GUIDs are not just specific to SQL server but are used across various platforms and technologies and can be used in database tables to ensure uniqueness. When globally unique identifiers are needed GUIDs are useful, but they come with storage cost as they are 16 bytes long. Using the current date and time as input, the NEWID() function creates a unique identifier. A new sequential GUID is automatically entered into the column by the NEWSEQUENTIALID() function, which is invoked automatically when a new record is inserted. Remember that sequential GUIDs are not as globally unique as completely random GUIDs, even if they may be more effective in some situations. In cases where adherence to global uniqueness is necessary, NEWID() may be a better option.