How to Create GUID in SQL: A Comprehensive Guide

In the world of database management, the need for unique identifiers is paramount. SQL Server provides a built-in function, NEWID(), to generate Globally Unique Identifiers (GUIDs), also known as Universally Unique Identifiers (UUIDs). This article delves into how to create GUIDs in SQL, exploring their syntax, usage, and practical examples.

Understanding GUIDs

GUIDs are 128-bit identifiers that are statistically unique. This means that the probability of generating the same GUID twice is extremely low, making them ideal for identifying records across different tables, databases, or even systems. They are commonly used as primary keys, foreign keys, or simply as unique identifiers for various entities within a database.

The NEWID() Function

The NEWID() function in SQL Server is a simple yet powerful tool for generating GUIDs. It takes no arguments and returns a new uniqueidentifier value each time it is called. The NEWID() function is compliant with RFC4122.

Syntax

The syntax for using the NEWID() function is straightforward:

NEWID ( )

Return Type

The NEWID() function returns a value of the uniqueidentifier data type.

Examples of Creating GUIDs in SQL

Let’s explore various ways to utilize the NEWID() function in SQL.

A. Assigning a GUID to a Variable

This example demonstrates how to declare a variable of type uniqueidentifier and assign a new GUID to it using NEWID().

-- Declaring a local variable
DECLARE @myid uniqueidentifier;

-- Assigning a new GUID to the variable
SET @myid = NEWID();

-- Displaying the value of the variable
PRINT 'Value of @myid is: ' + CONVERT(varchar(255), @myid);

The CONVERT function is used to convert the uniqueidentifier value to a string for display purposes.

B. Using NEWID() in a CREATE TABLE Statement

This example showcases how to create a table with a column of type uniqueidentifier and use NEWID() as the default value for that column. This ensures that each new row inserted into the table will automatically have a unique GUID assigned to it.

-- Creating a table with a GUID column
CREATE TABLE cust (
    CustomerID uniqueidentifier NOT NULL DEFAULT newid(),
    Company VARCHAR(30) NOT NULL,
    ContactName VARCHAR(60) NOT NULL,
    Address VARCHAR(30) NOT NULL,
    City VARCHAR(30) NOT NULL,
    StateProvince VARCHAR(10) NULL,
    PostalCode VARCHAR(10) NOT NULL,
    CountryRegion VARCHAR(20) NOT NULL,
    Telephone VARCHAR(15) NOT NULL,
    Fax VARCHAR(15) NULL
);
GO

-- Inserting sample rows into the table
INSERT cust (Company, ContactName, Address, City, StateProvince, PostalCode, CountryRegion, Telephone, Fax)
VALUES
('Wartian Herkku', 'Pirkko Koskitalo', 'Torikatu 38', 'Oulu', NULL, '90110', 'Finland', '981-443655', '981-443655'),
('Wellington Importadora', 'Paula Parente', 'Rua do Mercado, 12', 'Resende', 'SP', '08737-363', 'Brasil', '(14) 555-8122', ''),
('Cactus Comidas para Ilevar', 'Patricio Simpson', 'Cerrito 333', 'Buenos Aires', NULL, '1010', 'Argentina', '(1) 135-5555', '(1) 135-4892'),
('Ernst Handel', 'Roland Mendel', 'Kirchgasse 6', 'Graz', NULL, '8010', 'Austria', '7675-3425', '7675-3426'),
('Maison Dewey', 'Catherine Dewey', 'Rue Joseph-Bens 532', 'Bruxelles', NULL, 'B-1180', 'Belgium', '(02) 201 24 67', '(02) 201 24 68');
GO

C. Assigning a Specific GUID to a Variable

While NEWID() generates random GUIDs, you can also assign a specific GUID value to a variable. This is useful when you need to work with a known GUID.

-- Declaring a local variable
DECLARE @myid uniqueidentifier;

-- Assigning a specific GUID to the variable
SET @myid = 'A972C577-DFB0-064E-1189-0154C99310DAAC12';

-- Displaying the value of the variable
SELECT @myid;
GO

D. Querying Random Data Using NEWID()

The NEWID() function can also be used to retrieve random data from a table. This is achieved by using NEWID() in the ORDER BY clause.

-- Selecting a random record from the Production.Product table
SELECT TOP 1 ProductID, Name, ProductNumber
FROM Production.Product
ORDER BY NEWID();
GO

To retrieve more random records, simply increase the value specified in the TOP clause. This technique is valuable for testing or for implementing features like “random product” suggestions.

Best Practices for Using GUIDs

• Use GUIDs as Primary Keys: GUIDs are excellent candidates for primary keys, especially in distributed systems or when merging data from multiple sources. They avoid potential conflicts that can arise from using auto-incrementing integer IDs.
• Consider Performance: While GUIDs offer uniqueness, they can impact performance compared to integer-based primary keys, especially regarding index size and fragmentation. Consider using sequential GUIDs (combining a timestamp with a random component) to mitigate this.
• Choose the Right GUID Version: Different GUID versions exist, each generated using a specific algorithm. NEWID() generates version 4 GUIDs, which are randomly generated.

Conclusion

The NEWID() function provides a simple and effective way to generate GUIDs in SQL Server. Whether you’re creating unique identifiers for records, assigning default values to columns, or querying random data, NEWID() is a valuable tool in your SQL arsenal. Understanding its usage and considering best practices will help you leverage GUIDs effectively in your database design.