dotnet-sqlserver-best-practices

SQL Server Best Practices for .NET

Overview

Best practices for SQL Server database design, naming conventions, indexing, and performance optimization when using with .NET and Entity Framework Core.

Quick Reference

| Category | Best Practice | |----------|---------------| | Naming | PascalCase for tables/columns | | Primary Keys | Use uniqueidentifier (Guid) with NEWSEQUENTIALID() or int/bigint IDENTITY | | Timestamps | Use datetimeoffset with UTC | | Indexes | Index foreign keys, unique constraints | | Strings | Use nvarchar(n) with explicit lengths | | JSON | Use nvarchar(max) with JSON functions (SQL Server 2016+) |

Naming Conventions

PascalCase Standard

SQL Server convention is PascalCase for all identifiers:

-- PascalCase (SQL Server convention)
CREATE TABLE UserProfiles (
    UserId uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    FirstName nvarchar(100) NOT NULL,
    LastName nvarchar(100) NOT NULL,
    CreatedAt datetimeoffset NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedAt datetimeoffset NOT NULL DEFAULT SYSUTCDATETIME()
);

EF Core Setup

// src/{name}.infrastructure/DependencyInjection.cs
services.AddDbContext<ApplicationDbContext>(options =>
{
    options.UseSqlServer(connectionString);
    // EF Core uses PascalCase by default — matches SQL Server convention
});

Naming Patterns

| Object | Pattern | Example | |--------|---------|---------| | Tables | PascalCase (plural) | UserProfiles, OrderItems | | Columns | PascalCase | FirstName, CreatedAt | | Primary Keys | PK_{Table} | PK_Users, PK_Orders | | Foreign Keys | FK_{Table}_{RefTable} | FK_Orders_Users | | Indexes | IX_{Table}_{Column(s)} | IX_Users_Email | | Unique Indexes | UIX_{Table}_{Column(s)} | UIX_Users_Email | | Check Constraints | CK_{Table}_{Column} | CK_Users_Age | | Default Constraints | DF_{Table}_{Column} | DF_Users_CreatedAt | | Schemas | PascalCase | dbo, Sales, Identity |

Data Types

Recommended Types

| C# Type | SQL Server Type | Notes | |---------|-----------------|-------| | Guid | uniqueidentifier | Use NEWSEQUENTIALID() to avoid index fragmentation | | string | nvarchar(n) | Always specify length; Unicode by default | | string (large) | nvarchar(max) | Only when > 4000 characters needed | | int | int | 4 bytes, -2B to +2B | | long | bigint | 8 bytes | | decimal | decimal(p,s) | Exact precision for money | | double | float | Floating point | | bool | bit | 0/1 | | DateTime | datetimeoffset | Always use with time zone | | DateTime (date only) | date | When time is not needed | | byte[] | varbinary(max) | Binary data | | byte[] (concurrency) | rowversion | Auto-increment on update |

nvarchar vs varchar

-- nvarchar: Unicode (2 bytes per char) - PREFERRED
CREATE TABLE Products (
    Id uniqueidentifier PRIMARY KEY,
    Name nvarchar(200) NOT NULL,        -- Supports international characters
    Description nvarchar(2000)
);

-- varchar: Non-Unicode (1 byte per char) - only for ASCII-only data
CREATE TABLE AuditLogs (
    Id bigint IDENTITY PRIMARY KEY,
    Action varchar(50) NOT NULL          -- Known ASCII values like 'INSERT', 'UPDATE'
);

Always specify length:

• nvarchar(max) can't be indexed and has performance implications
• Use the smallest length that fits the domain (email: 256, name: 100, etc.)

Timestamps

-- datetimeoffset with UTC default
CreatedAt datetimeoffset NOT NULL CONSTRAINT DF_Users_CreatedAt DEFAULT SYSUTCDATETIME(),
UpdatedAt datetimeoffset NOT NULL CONSTRAINT DF_Users_UpdatedAt DEFAULT SYSUTCDATETIME()

-- datetime2 alternative (no timezone, but higher precision than datetime)
CreatedAt datetime2(7) NOT NULL DEFAULT SYSUTCDATETIME()

-- AVOID legacy datetime type
-- CreatedAt datetime NOT NULL  -- Lower precision, limited range

// EF Core configuration
builder.Property(e => e.CreatedAt)
    .HasColumnType("datetimeoffset")
    .IsRequired()
    .HasDefaultValueSql("SYSUTCDATETIME()");

JSON Support (SQL Server 2016+)

-- Store JSON in nvarchar(max)
CREATE TABLE Products (
    Id uniqueidentifier PRIMARY KEY,
    Metadata nvarchar(max) NULL,
    CONSTRAINT CK_Products_Metadata_JSON CHECK (ISJSON(Metadata) = 1)
);

-- Query JSON
SELECT Id, JSON_VALUE(Metadata, '$.category') AS Category
FROM Products
WHERE JSON_VALUE(Metadata, '$.isActive') = 'true';

// EF Core configuration
builder.Property(e => e.Metadata)
    .HasColumnType("nvarchar(max)");

// EF Core 7+ owned types as JSON
builder.OwnsOne(e => e.Settings, settingsBuilder =>
{
    settingsBuilder.ToJson();
});

Primary Keys

Sequential GUID - Recommended

-- NEWSEQUENTIALID() avoids index fragmentation (page splits)
CREATE TABLE Users (
    Id uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    Email nvarchar(256) NOT NULL
);

// EF Core - let SQL Server generate sequential GUIDs
builder.Property(e => e.Id)
    .HasDefaultValueSql("NEWSEQUENTIALID()");

// In domain entity - app generates (use sequential GUID library)
public static User Create(...)
{
    return new User(Guid.NewGuid(), ...);  // Or use RT.Comb for sequential
}

Why NEWSEQUENTIALID() over NEWID()?

• NEWID() generates random GUIDs causing clustered index fragmentation
• NEWSEQUENTIALID() generates sequential GUIDs for efficient inserts
• 16 bytes vs 4 bytes (int) — tradeoff for global uniqueness

IDENTITY Alternative

-- Auto-increment (best for clustered index performance)
CREATE TABLE Orders (
    Id int IDENTITY(1,1) PRIMARY KEY,
    OrderNumber nvarchar(20) NOT NULL
);

-- bigint for high-volume tables
CREATE TABLE AuditLogs (
    Id bigint IDENTITY(1,1) PRIMARY KEY
);

// EF Core
builder.Property(e => e.Id)
    .UseIdentityColumn();  // SQL Server IDENTITY

Indexing Strategies

Index Foreign Keys

-- SQL Server does NOT auto-create FK indexes (unlike some ORMs)
CREATE NONCLUSTERED INDEX IX_Orders_UserId ON Orders(UserId);
CREATE NONCLUSTERED INDEX IX_OrderItems_OrderId ON OrderItems(OrderId);

builder.HasIndex(o => o.UserId);

Unique Indexes

CREATE UNIQUE NONCLUSTERED INDEX UIX_Users_Email ON Users(Email);

builder.HasIndex(u => u.Email).IsUnique();

Composite Indexes

-- Composite indexes for common query patterns
CREATE NONCLUSTERED INDEX IX_Orders_UserId_Status ON Orders(UserId, Status);
CREATE NONCLUSTERED INDEX IX_Orders_CreatedAt_Status ON Orders(CreatedAt DESC, Status);

Order matters! Index on (UserId, Status) helps:

• WHERE UserId = @id AND Status = @status - uses index
• WHERE UserId = @id - uses index
• WHERE Status = @status - does NOT use index

Filtered Indexes

-- Index only relevant rows (equivalent to PostgreSQL partial indexes)
CREATE NONCLUSTERED INDEX IX_Orders_Pending 
ON Orders(CreatedAt) 
WHERE Status = 'Pending';

CREATE NONCLUSTERED INDEX IX_Users_Active_Email 
ON Users(Email) 
WHERE IsDeleted = 0;

// EF Core
builder.HasIndex(o => o.CreatedAt)
    .HasFilter("[Status] = 'Pending'");

Covering Indexes (INCLUDE)

-- Include frequently accessed columns to avoid key lookups
CREATE NONCLUSTERED INDEX IX_Users_Email_Include 
ON Users(Email) 
INCLUDE (FirstName, LastName);

// EF Core 7+
builder.HasIndex(u => u.Email)
    .IncludeProperties(u => new { u.FirstName, u.LastName });

Full-Text Search Indexes

-- Create full-text catalog and index
CREATE FULLTEXT CATALOG ProductSearch AS DEFAULT;

CREATE FULLTEXT INDEX ON Products(Name, Description)
KEY INDEX PK_Products
WITH CHANGE_TRACKING AUTO;

-- Query using CONTAINS or FREETEXT
SELECT * FROM Products
WHERE CONTAINS((Name, Description), '"search term" OR FORMSOF(INFLECTIONAL, "search")');

SELECT * FROM Product