How to Create UUID in SQL Server

-- Generate a random UUID
SELECT NEWID() AS new_uuid;
-- Result: 550E8400-E29B-41D4-A716-446655440000

-- Create table with UNIQUEIDENTIFIER primary key
CREATE TABLE users (
    id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    name NVARCHAR(100) NOT NULL,
    email NVARCHAR(255) UNIQUE NOT NULL,
    created_at DATETIME2 DEFAULT GETDATE()
);

-- Insert without specifying ID (auto-generated)
INSERT INTO users (name, email) 
VALUES ('John Doe', 'john@example.com');

-- Query by UUID
SELECT * FROM users 
WHERE id = '550E8400-E29B-41D4-A716-446655440000';

-- Generate multiple UUIDs
SELECT NEWID() AS uuid1, NEWID() AS uuid2, NEWID() AS uuid3;

-- Create table with sequential GUID
CREATE TABLE products (
    id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    name NVARCHAR(200) NOT NULL,
    sku NVARCHAR(50) UNIQUE,
    price DECIMAL(10,2)
);

-- Insert products (IDs generated sequentially)
INSERT INTO products (name, sku, price) 
VALUES 
    ('Laptop', 'LAP-001', 999.99),
    ('Mouse', 'MOU-001', 29.99),
    ('Keyboard', 'KEY-001', 79.99);

-- View sequential GUIDs
SELECT id, name FROM products ORDER BY id;

-- UNIQUEIDENTIFIER (16 bytes) - RECOMMENDED
CREATE TABLE orders_guid (
    id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    customer_id UNIQUEIDENTIFIER NOT NULL,
    total DECIMAL(10,2)
);

-- NVARCHAR(36) (72 bytes) - NOT RECOMMENDED
CREATE TABLE orders_string (
    id NVARCHAR(36) PRIMARY KEY,
    customer_id NVARCHAR(36) NOT NULL,
    total DECIMAL(10,2)
);

-- Check storage size
SELECT 
    DATALENGTH(CAST('550E8400-E29B-41D4-A716-446655440000' AS UNIQUEIDENTIFIER)) AS guid_bytes,
    DATALENGTH(CAST('550E8400-E29B-41D4-A716-446655440000' AS NVARCHAR(36))) AS string_bytes;
-- Result: guid_bytes=16, string_bytes=72

-- Clustered index on sequential GUID (recommended)
CREATE TABLE orders (
    id UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED DEFAULT NEWSEQUENTIALID(),
    customer_id UNIQUEIDENTIFIER NOT NULL,
    order_date DATETIME2 DEFAULT GETDATE(),
    total DECIMAL(10,2)
);

-- Non-clustered index on foreign key
CREATE NONCLUSTERED INDEX IX_orders_customer 
ON orders(customer_id);

-- Composite index
CREATE NONCLUSTERED INDEX IX_orders_customer_date 
ON orders(customer_id, order_date DESC);

-- Include columns for covering index
CREATE NONCLUSTERED INDEX IX_orders_customer_covering
ON orders(customer_id)
INCLUDE (order_date, total);

-- Check index fragmentation
SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id 
    AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- String to UNIQUEIDENTIFIER
DECLARE @guid_string NVARCHAR(36) = '550E8400-E29B-41D4-A716-446655440000';
DECLARE @guid UNIQUEIDENTIFIER = CAST(@guid_string AS UNIQUEIDENTIFIER);

SELECT @guid AS converted_guid;

-- UNIQUEIDENTIFIER to string (uppercase)
SELECT CAST(NEWID() AS NVARCHAR(36)) AS guid_string;

-- UNIQUEIDENTIFIER to string (lowercase)
SELECT LOWER(CAST(NEWID() AS NVARCHAR(36))) AS guid_lowercase;

-- Remove hyphens
SELECT REPLACE(CAST(NEWID() AS NVARCHAR(36)), '-', '') AS guid_no_hyphens;

-- Binary representation
SELECT CAST(NEWID() AS BINARY(16)) AS guid_binary;

-- Procedure to create user and return GUID
CREATE PROCEDURE sp_CreateUser
    @name NVARCHAR(100),
    @email NVARCHAR(255),
    @new_id UNIQUEIDENTIFIER OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    SET @new_id = NEWID();
    
    INSERT INTO users (id, name, email)
    VALUES (@new_id, @name, @email);
    
    SELECT @new_id AS user_id;
END;
GO

-- Execute procedure
DECLARE @user_id UNIQUEIDENTIFIER;
EXEC sp_CreateUser 
    @name = 'Alice Smith',
    @email = 'alice@example.com',
    @new_id = @user_id OUTPUT;

SELECT @user_id AS created_user_id;

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;

public class User
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }
    
    [Required]
    [MaxLength(100)]
    public string Name { get; set; }
    
    [Required]
    [MaxLength(255)]
    public string Email { get; set; }
    
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
}

public class AppDbContext : DbContext
{
    public DbSet Users { get; set; }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity()
            .Property(u => u.Id)
            .HasDefaultValueSql("NEWSEQUENTIALID()");
    }
}

// Usage
using (var context = new AppDbContext())
{
    var user = new User 
    { 
        Name = "Bob Wilson", 
        Email = "bob@example.com" 
    };
    
    context.Users.Add(user);
    await context.SaveChangesAsync();
    
    Console.WriteLine($"Created user with ID: {user.Id}");
}

using System;
using System.Data.SqlClient;

public class UserRepository
{
    private string connectionString;
    
    public Guid CreateUser(string name, string email)
    {
        using (var conn = new SqlConnection(connectionString))
        {
            conn.Open();
            
            var cmd = new SqlCommand(
                "INSERT INTO users (name, email) OUTPUT INSERTED.id VALUES (@name, @email)", 
                conn);
            
            cmd.Parameters.AddWithValue("@name", name);
            cmd.Parameters.AddWithValue("@email", email);
            
            Guid userId = (Guid)cmd.ExecuteScalar();
            return userId;
        }
    }
    
    public User GetUserById(Guid id)
    {
        using (var conn = new SqlConnection(connectionString))
        {
            conn.Open();
            
            var cmd = new SqlCommand(
                "SELECT id, name, email FROM users WHERE id = @id", 
                conn);
            
            cmd.Parameters.Add("@id", System.Data.SqlDbType.UniqueIdentifier).Value = id;
            
            using (var reader = cmd.ExecuteReader())
            {
                if (reader.Read())
                {
                    return new User
                    {
                        Id = reader.GetGuid(0),
                        Name = reader.GetString(1),
                        Email = reader.GetString(2)
                    };
                }
            }
        }
        return null;
    }
}