Generate UUID in SQL Server

SQL Server provides built-in UUID generation through the NEWID() and NEWSEQUENTIALID() functions. UUIDs are stored in UNIQUEIDENTIFIER columns:

-- Generate random UUID (version 4)
SELECT NEWID() AS RandomUUID;
-- Result: 6F9619FF-8B86-D011-B42D-00C04FC964FF

-- Generate sequential UUID (better for indexes)
SELECT NEWSEQUENTIALID() AS SequentialUUID;
-- Result: 6F9619FF-8B86-D011-B42D-00C04FC964F0

SQL Server provides two main UUID generation functions with different characteristics:

-- NEWID() - Random UUID (similar to UUID v4)
SELECT NEWID() AS random_uuid;

-- NEWSEQUENTIALID() - Sequential UUID (optimized for indexing)
-- Can only be used as DEFAULT constraint, not in SELECT
-- Creates UUIDs that are sequential for better index performance

-- Convert string to UNIQUEIDENTIFIER
SELECT CAST('6F9619FF-8B86-D011-B42D-00C04FC964FF' AS UNIQUEIDENTIFIER);

-- Convert UNIQUEIDENTIFIER to string
SELECT CAST(NEWID() AS VARCHAR(36));

Table Schema with UUID Primary Keys

Basic UNIQUEIDENTIFIER table setup:

-- Using NEWID() for random UUIDs
CREATE TABLE Users (
    Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    Name NVARCHAR(100) NOT NULL,
    Email NVARCHAR(255) UNIQUE NOT NULL,
    CreatedAt DATETIME2 DEFAULT GETUTCDATE()
);

-- Insert data (UUID generated automatically)
INSERT INTO Users (Name, Email) 
VALUES ('John Doe', '[email protected]');

-- Query with UUID
SELECT * FROM Users 
WHERE Id = '6F9619FF-8B86-D011-B42D-00C04FC964FF';

Optimized with NEWSEQUENTIALID():

-- Using NEWSEQUENTIALID() for better index performance
CREATE TABLE Orders (
    Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    UserId UNIQUEIDENTIFIER NOT NULL,
    OrderNumber NVARCHAR(50),
    Total DECIMAL(10,2),
    CreatedAt DATETIME2 DEFAULT GETUTCDATE(),
    
    FOREIGN KEY (UserId) REFERENCES Users(Id),
    INDEX IX_Orders_UserId (UserId),
    INDEX IX_Orders_CreatedAt (CreatedAt)
);

-- Insert with explicit UUID
INSERT INTO Orders (Id, UserId, OrderNumber, Total) 
VALUES (
    NEWID(),
    '6F9619FF-8B86-D011-B42D-00C04FC964FF',
    'ORD-001',
    99.99
);

Advanced SQL Server UUID Usage

UUID utility functions:

-- Create function to validate UUID format
CREATE FUNCTION dbo.IsValidUUID(@uuid NVARCHAR(36))
RETURNS BIT
AS
BEGIN
    DECLARE @result BIT = 0;
    
    BEGIN TRY
        DECLARE @test UNIQUEIDENTIFIER = CAST(@uuid AS UNIQUEIDENTIFIER);
        SET @result = 1;
    END TRY
    BEGIN CATCH
        SET @result = 0;
    END CATCH
    
    RETURN @result;
END;

-- Test UUID validation
SELECT dbo.IsValidUUID('6F9619FF-8B86-D011-B42D-00C04FC964FF') AS IsValid;
SELECT dbo.IsValidUUID('invalid-uuid') AS IsInvalid;

UUID version detection:

-- Extract UUID version (approximate)
CREATE FUNCTION dbo.GetUUIDVersion(@uuid UNIQUEIDENTIFIER)
RETURNS INT
AS
BEGIN
    DECLARE @uuidString NVARCHAR(36) = CAST(@uuid AS NVARCHAR(36));
    DECLARE @versionChar NCHAR(1) = SUBSTRING(@uuidString, 15, 1);
    
    RETURN CASE 
        WHEN @versionChar IN ('1') THEN 1
        WHEN @versionChar IN ('2') THEN 2
        WHEN @versionChar IN ('3') THEN 3
        WHEN @versionChar IN ('4') THEN 4
        WHEN @versionChar IN ('5') THEN 5
        ELSE 0
    END;
END;

-- Get UUID version
SELECT dbo.GetUUIDVersion(NEWID()) AS Version;

Performance Optimization

Clustered vs Non-Clustered indexes with UUIDs:

-- Avoid clustered index on random UUID (causes fragmentation)
CREATE TABLE BadExample (
    Id UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED DEFAULT NEWID(), -- BAD
    Data NVARCHAR(MAX)
);

-- Better: Use NEWSEQUENTIALID() for clustered index
CREATE TABLE GoodExample (
    Id UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED DEFAULT NEWSEQUENTIALID(), -- GOOD
    Data NVARCHAR(MAX)
);

-- Best: Use IDENTITY + UUID for optimal performance
CREATE TABLE BestExample (
    Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, -- Sequential primary key
    UUID UNIQUEIDENTIFIER UNIQUE DEFAULT NEWID(), -- UUID for external references
    Data NVARCHAR(MAX),
    
    INDEX IX_BestExample_UUID (UUID) -- Non-clustered index on UUID
);

Partitioning with UUIDs:

-- Create partition function for UUID
CREATE PARTITION FUNCTION UUIDPartitionFunction (UNIQUEIDENTIFIER)
AS RANGE LEFT FOR VALUES (
    '40000000-0000-0000-0000-000000000000',
    '80000000-0000-0000-0000-000000000000',
    'C0000000-0000-0000-0000-000000000000'
);

-- Create partition scheme
CREATE PARTITION SCHEME UUIDPartitionScheme
AS PARTITION UUIDPartitionFunction
TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]);

-- Create partitioned table
CREATE TABLE PartitionedData (
    Id UNIQUEIDENTIFIER DEFAULT NEWID(),
    Data NVARCHAR(MAX),
    CreatedAt DATETIME2 DEFAULT GETUTCDATE()
) ON UUIDPartitionScheme(Id);

Entity Framework Integration

Code First approach:

-- Entity Framework will generate similar schema
CREATE TABLE AspNetUsers (
    Id NVARCHAR(450) PRIMARY KEY, -- EF uses string by default
    UserName NVARCHAR(256),
    Email NVARCHAR(256),
    -- ... other Identity columns
);

-- Better: Configure EF to use UNIQUEIDENTIFIER
CREATE TABLE Users (
    Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    UserName NVARCHAR(256),
    Email NVARCHAR(256),
    CreatedAt DATETIME2 DEFAULT GETUTCDATE()
);

Stored procedures with UUIDs:

-- Create user with UUID
CREATE PROCEDURE CreateUser
    @Name NVARCHAR(100),
    @Email NVARCHAR(255),
    @UserId UNIQUEIDENTIFIER OUTPUT
AS
BEGIN
    SET @UserId = NEWID();
    
    INSERT INTO Users (Id, Name, Email)
    VALUES (@UserId, @Name, @Email);
    
    SELECT @UserId AS NewUserId;
END;

-- Execute procedure
DECLARE @NewUserId UNIQUEIDENTIFIER;
EXEC CreateUser 
    @Name = 'Jane Doe', 
    @Email = '[email protected]', 
    @UserId = @NewUserId OUTPUT;
SELECT @NewUserId;

Migration and Data Import

Converting existing tables to UUID:

-- Add UUID column to existing table
ALTER TABLE ExistingTable 
ADD UUIDColumn UNIQUEIDENTIFIER DEFAULT NEWID();

-- Update existing rows with UUIDs
UPDATE ExistingTable 
SET UUIDColumn = NEWID() 
WHERE UUIDColumn IS NULL;

-- Make UUID column NOT NULL
ALTER TABLE ExistingTable 
ALTER COLUMN UUIDColumn UNIQUEIDENTIFIER NOT NULL;

-- Add unique constraint
ALTER TABLE ExistingTable 
ADD CONSTRAINT UK_ExistingTable_UUID UNIQUE (UUIDColumn);

Bulk operations with UUIDs:

-- Bulk insert with UUIDs
INSERT INTO Users (Id, Name, Email)
SELECT 
    NEWID(),
    'User ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NVARCHAR(10)),
    'user' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NVARCHAR(10)) + '@example.com'
FROM sys.all_columns
CROSS JOIN sys.all_columns
WHERE ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) <= 1000;

-- Bulk update UUIDs
UPDATE Users 
SET Id = NEWID() 
WHERE Id IS NULL;

Replication and Always On

UUID considerations for replication:

-- UUIDs are perfect for merge replication (no conflicts)
CREATE TABLE ReplicatedTable (
    Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    Data NVARCHAR(MAX),
    ModifiedAt DATETIME2 DEFAULT GETUTCDATE(),
    
    -- Add rowguid for merge replication
    rowguid UNIQUEIDENTIFIER ROWGUIDCOL DEFAULT NEWID()
);

-- Always On Availability Groups work well with UUIDs
-- No special configuration needed for UUID columns

UUID Version Comparison

Choose the right approach for your SQL Server application:

• NEWID() - Random UUID (similar to Version 4)
• NEWSEQUENTIALID() - Sequential UUID for better performance
• Custom functions - For specific Version 1, Version 3, or Version 5

For SQL Server applications:

• Primary keys: Use NEWSEQUENTIALID() for better index performance
• External references: Use NEWID() for maximum uniqueness
• Replication: UUIDs work excellently with merge replication

How do I generate UUID in other databases?

SQL databases:

• PostgreSQL - Comprehensive UUID support with extensions
• MySQL - Built-in UUID() function
• SQLite - Custom functions and extensions
• Oracle - SYS_GUID() function

NoSQL databases:

• MongoDB - ObjectId and UUID generation
• Redis - UUID generation with Lua scripts
• Cassandra - Built-in UUID functions

← Back to Online UUID Generator