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:

```sql
-- 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

```csharp
// 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

```sql
-- 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

```sql
-- 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
```

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

### JSON Support (SQL Server 2016+)

```sql
-- 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';
```

```csharp
// 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

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

```csharp
// 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

```sql
-- 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
);
```

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

---

## Indexing Str