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 fragmentationNEWSEQUENTIALID() 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 Strategies
Index Foreign Keys
sql
-- 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);
csharp
builder.HasIndex(o => o.UserId);
Unique Indexes
sql
CREATE UNIQUE NONCLUSTERED INDEX UIX_Users_Email ON Users(Email);
csharp
builder.HasIndex(u => u.Email).IsUnique();
Composite Indexes
sql
-- 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 indexWHERE UserId = @id - uses indexWHERE Status = @status - does NOT use index
Filtered Indexes
sql
-- 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;
csharp
// EF Core
builder.HasIndex(o => o.CreatedAt)
.HasFilter("[Status] = 'Pending'");
Covering Indexes (INCLUDE)
sql
-- Include frequently accessed columns to avoid key lookups
CREATE NONCLUSTERED INDEX IX_Users_Email_Include
ON Users(Email)
INCLUDE (FirstName, LastName);
csharp
// EF Core 7+
builder.HasIndex(u => u.Email)
.IncludeProperties(u => new { u.FirstName, u.LastName });
Full-Text Search Indexes
sql
-- 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 Products
WHERE FREETEXT((Name, Description), 'search term');
Columnstore Indexes (Analytics)
sql
-- Nonclustered columnstore for analytical queries on OLTP tables
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Orders_Analytics
ON Orders(CreatedAt, Status, TotalAmount, UserId);
-- Ideal for aggregation queries
SELECT Status, COUNT(*) AS OrderCount, SUM(TotalAmount) AS Total
FROM Orders
GROUP BY Status;
When NOT to Index
Do not index:
- Very small tables (< 1000 rows)
- Columns rarely used in WHERE/JOIN/ORDER BY
- Columns with low cardinality (few distinct values like bit columns)
- Columns that change frequently (high write overhead)
- Wide columns (nvarchar(max), varbinary(max))
Constraints
Primary Key Constraints
sql
CONSTRAINT PK_Users PRIMARY KEY CLUSTERED (Id)
csharp
builder.HasKey(u => u.Id);
Foreign Key Constraints
sql
-- Named foreign keys with appropriate delete behavior
CONSTRAINT FK_Orders_Users
FOREIGN KEY (UserId)
REFERENCES Users(Id)
ON DELETE NO ACTION, -- Prevent orphans (SQL Server default)
CONSTRAINT FK_OrderItems_Orders
FOREIGN KEY (OrderId)
REFERENCES Orders(Id)
ON DELETE CASCADE -- Delete items with order
csharp
builder.HasOne(o => o.User)
.WithMany(u => u.Orders)
.HasForeignKey(o => o.UserId)
.OnDelete(DeleteBehavior.Restrict);
Check Constraints
sql
-- Enforce business rules at database level
CONSTRAINT CK_Users_Age CHECK (Age >= 18 AND Age <= 120),
CONSTRAINT CK_Products_Price CHECK (Price >= 0),
CONSTRAINT CK_Orders_Quantity CHECK (Quantity > 0)
csharp
builder.ToTable(t => t.HasCheckConstraint(
"CK_Products_Price",
"[Price] >= 0"));
Unique Constraints
sql
-- Composite unique constraints
CONSTRAINT UQ_UserProfiles_UserType UNIQUE (UserId, ProfileType)
csharp
builder.HasIndex(p => new { p.UserId, p.ProfileType }).IsUnique();
Performance Optimization
Connection Pooling
csharp
// Connection string with pooling (enabled by default)
"Server=localhost;Database=MyDb;User Id=app_user;Password=pass;Encrypt=True;TrustServerCertificate=True;Min Pool Size=5;Max Pool Size=100"
Batch Operations
csharp
// EF Core batches SaveChanges automatically in SQL Server
context.Users.AddRange(users);
await context.SaveChangesAsync(); // Batched into fewer round-trips
// Do NOT save one at a time
foreach (var user in users)
{
context.Users.Add(user);
await context.SaveChangesAsync(); // Multiple round-trips!
}
AsNoTracking for Read-Only Queries
csharp
// Read-only queries
var users = await context.Users
.AsNoTracking() // Faster, no change tracking overhead
.ToListAsync();
Compiled Queries
csharp
// Define once
private static readonly Func<ApplicationDbContext, string, Task<User?>> GetUserByEmail =
EF.CompileAsyncQuery((ApplicationDbContext context, string email) =>
context.Users.FirstOrDefault(u => u.Email == email));
// Use many times (faster — skips query compilation)
var user = await GetUserByEmail(context, email);
Pagination
sql
-- Efficient pagination (SQL Server 2012+)
SELECT * FROM Users
ORDER BY CreatedAt DESC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;
csharp
// EF Core pagination
var users = await context.Users
.OrderByDescending(u => u.CreatedAt)
.Skip(pageNumber * pageSize)
.Take(pageSize)
.ToListAsync();
Avoid N+1 Queries
csharp
// N+1 problem
var orders = await context.Orders.ToListAsync();
foreach (var order in orders)
{
var user = await context.Users.FindAsync(order.UserId); // N queries!
}
// Eager loading
var orders = await context.Orders
.Include(o => o.User) // Single query with JOIN
.ToListAsync();