dotnet-sqlserver-best-practices

.NET 的 SQL Server 最佳实践

概述

使用 .NET 和 Entity Framework Core 时，SQL Server 数据库设计、命名约定、索引和性能优化的最佳实践。

快速参考

类别	最佳实践
命名	表和列使用 PascalCase
主键	使用 uniqueidentifier（Guid）和 NEWSEQUENTIALID()，或使用 int/bigint IDENTITY
时间戳	使用 UTC 时间的 datetimeoffset
索引	为外键和唯一约束建立索引
字符串	使用明确指定长度的 nvarchar(n)
JSON	使用 nvarchar(max) 和 JSON 函数（SQL Server 2016+）

---

命名约定

PascalCase 标准

SQL Server 的约定是所有标识符均使用 PascalCase：

-- 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 设置

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

命名模式

对象	模式	示例
表	PascalCase（复数）	UserProfiles、OrderItems
列	PascalCase	FirstName、CreatedAt
主键	PK_{Table}	PK_Users、PK_Orders
外键	FK_{Table}_{RefTable}	FK_Orders_Users
索引	IX_{Table}_{Column(s)}	IX_Users_Email
唯一索引	UIX_{Table}_{Column(s)}	UIX_Users_Email
检查约束	CK_{Table}_{Column}	CK_Users_Age
默认约束	DF_{Table}_{Column}	DF_Users_CreatedAt
架构	PascalCase	dbo、Sales、Identity

---

数据类型

推荐类型

C# 类型	SQL Server 类型	备注
Guid	uniqueidentifier	使用 NEWSEQUENTIALID()，避免索引碎片
string	nvarchar(n)	始终指定长度；默认支持 Unicode
string（较长）	nvarchar(max)	仅在需要超过 4000 个字符时使用
int	int	4 字节，-2B 至 +2B
long	bigint	8 字节
decimal	decimal(p,s)	为货币提供精确精度
double	float	浮点数
bool	bit	0/1
DateTime	datetimeoffset	始终使用带时区的类型
DateTime（仅日期）	date	不需要时间时使用
byte[]	varbinary(max)	二进制数据
byte[]（并发）	rowversion	更新时自动递增

nvarchar 与 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'
);

始终指定长度：

• nvarchar(max) 无法建立索引，并会影响性能
• 使用符合数据域要求的最小长度（电子邮件：256，姓名：100 等）

时间戳

-- 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 支持（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();
});

---

主键

顺序 GUID——推荐

-- 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
}

为什么选择 NEWSEQUENTIALID() 而不是 NEWID()？

• NEWID() 会生成随机 GUID，导致聚集索引碎片
• NEWSEQUENTIALID() 会生成顺序 GUID，便于高效插入
• 16 字节，而 int 为 4 字节——以此换取全局唯一性

IDENTITY 替代方案

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

---

索引策略

为外键建立索引

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

唯一索引

CREATE UNIQUE NONCLUSTERED INDEX UIX_Users_Email ON Users(Email);

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

复合索引

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

顺序很重要！ (UserId, Status) 上的索引适用于：

• WHERE UserId = @id AND Status = @status——使用索引
• WHERE UserId = @id——使用索引
• WHERE Status = @status——不使用索引

筛选索引

-- 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'");

覆盖索引（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 });

全文搜索索引

-- 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');

列存储索引（分析）

-- 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;

不应建立索引的情况

以下情况不要建立索引：

• 非常小的表（少于 1000 行）
• 很少用于 WHERE/JOIN/ORDER BY 的列
• 基数较低的列（不同值很少，例如 bit 列）
• 经常更改的列（写入开销较高）
• 宽列（nvarchar(max)、varbinary(max)）

---

约束

主键约束

CONSTRAINT PK_Users PRIMARY KEY CLUSTERED (Id)

builder.HasKey(u => u.Id);

外键约束

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

builder.HasOne(o => o.User)
    .WithMany(u => u.Orders)
    .HasForeignKey(o => o.UserId)
    .OnDelete(DeleteBehavior.Restrict);

检查约束

-- 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)

builder.ToTable(t => t.HasCheckConstraint(
    "CK_Products_Price", 
    "[Price] >= 0"));

唯一约束

-- Composite unique constraints
CONSTRAINT UQ_UserProfiles_UserType UNIQUE (UserId, ProfileType)

builder.HasIndex(p => new { p.UserId, p.ProfileType }).IsUnique();

---

性能优化

连接池

// 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"

批量操作

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

// Read-only queries
var users = await context.Users
    .AsNoTracking()  // Faster, no change tracking overhead
    .ToListAsync();

编译查询

// 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);

分页

-- Efficient pagination (SQL Server 2012+)
SELECT * FROM Users
ORDER BY CreatedAt DESC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;

// EF Core pagination
var users = await context.Users
    .OrderByDescending(u => u.CreatedAt)
    .Skip(pageNumber * pageSize)
    .Take(pageSize)
    .ToListAsync();

避免 N+1 查询

// 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();

// Split query for multiple collections
var orders = await context.Orders
    .Include(o => o.Items)
    .Include(o => o.Payments)
    .AsSplitQuery()  // Separate queries, avoids cartesian explosion
    .ToListAsync();

查询提示

// NOLOCK (read uncommitted) — use sparingly, only for non-critical reads
var reports = await context.Orders
    .TagWith("OPTION (MAXDOP 4)")
    .AsNoTracking()
    .ToListAsync();

// Using raw SQL with hints
var orders = await context.Orders
    .FromSqlRaw("SELECT * FROM Orders WITH (NOLOCK) WHERE Status = {0}", status)
    .ToListAsync();

---

维护

更新统计信息

-- Update statistics for a table
UPDATE STATISTICS Users;

-- Update all statistics in the database
EXEC sp_updatestats;

-- Full scan for more accurate statistics
UPDATE STATISTICS Users WITH FULLSCAN;

索引维护

-- 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, 'LIMITED') 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
    AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- Reorganize (< 30% fragmentation)
ALTER INDEX IX_Orders_UserId ON Orders REORGANIZE;

-- Rebuild (> 30% fragmentation)
ALTER INDEX IX_Orders_UserId ON Orders REBUILD WITH (ONLINE = ON);

-- Rebuild all indexes on a table
ALTER INDEX ALL ON Orders REBUILD WITH (ONLINE = ON);

监控索引使用情况

-- Find unused indexes
SELECT 
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius 
    ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
    AND i.type_desc = 'NONCLUSTERED'
    AND ISNULL(ius.user_seeks, 0) + ISNULL(ius.user_scans, 0) + ISNULL(ius.user_lookups, 0) = 0
ORDER BY ius.user_updates DESC;

-- Find missing indexes (suggested by query optimizer)
SELECT TOP 20
    OBJECT_NAME(mid.object_id) AS TableName,
    migs.avg_user_impact,
    migs.user_seeks,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY migs.avg_user_impact * migs.user_seeks DESC;

数据库大小

-- Check table sizes
SELECT 
    t.name AS TableName,
    s.row_count AS RowCount,
    CAST(SUM(a.total_pages) * 8 / 1024.0 AS DECIMAL(10,2)) AS TotalSpaceMB,
    CAST(SUM(a.used_pages) * 8 / 1024.0 AS DECIMAL(10,2)) AS UsedSpaceMB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT JOIN sys.dm_db_partition_stats s ON p.partition_id = s.partition_id AND p.index_id = s.index_id
GROUP BY t.name, s.row_count
ORDER BY SUM(a.total_pages) DESC;

---

安全最佳实践

连接安全

// Use encryption
"Server=prod-server;Database=MyDb;User Id=app_user;Password=pass;Encrypt=True;TrustServerCertificate=False"

// Windows Authentication (preferred in domain environments)
"Server=localhost;Database=MyDb;Integrated Security=True;Encrypt=True"

// Azure SQL with Managed Identity
"Server=myserver.database.windows.net;Database=MyDb;Authentication=Active Directory Managed Identity"

最小权限

-- Create application-specific login and user
CREATE LOGIN app_login WITH PASSWORD = 'SecurePassword123!';
CREATE USER app_user FOR LOGIN app_login;

-- Grant only necessary permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO app_user;

-- For EF Core migrations (separate admin user)
CREATE USER migration_user FOR LOGIN migration_login;
ALTER ROLE db_ddladmin ADD MEMBER migration_user;

-- Do NOT use sa or db_owner for application

参数化查询

// EF Core uses parameters automatically (safe from SQL injection)
var users = await context.Users
    .Where(u => u.Email == email)  // Parameterized
    .ToListAsync();

// Dapper with parameters
var users = await connection.QueryAsync<User>(
    "SELECT * FROM Users WHERE Email = @Email",
    new { Email = email });

// NEVER concatenate SQL
var sql = $"SELECT * FROM Users WHERE Email = '{email}'";  // SQL INJECTION!

---

SQL Server 功能

时态表（系统版本控制）

-- Automatic history tracking (SQL Server 2016+)
CREATE TABLE Products (
    Id uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    Name nvarchar(200) NOT NULL,
    Price decimal(18,2) NOT NULL,
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductsHistory));

-- Query historical data
SELECT * FROM Products FOR SYSTEM_TIME AS OF '2024-01-15T10:00:00';

-- Query changes in a range
SELECT * FROM Products FOR SYSTEM_TIME BETWEEN '2024-01-01' AND '2024-02-01';

// EF Core 6+ temporal table support
builder.ToTable("Products", b => b.IsTemporal());

// Query historical data
var historicalProducts = await context.Products
    .TemporalAsOf(DateTime.UtcNow.AddDays(-7))
    .ToListAsync();

序列

-- Create a sequence for order numbers
CREATE SEQUENCE dbo.OrderNumberSequence
    AS int
    START WITH 1000
    INCREMENT BY 1;

-- Use in table
CREATE TABLE Orders (
    Id uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    OrderNumber int NOT NULL DEFAULT NEXT VALUE FOR dbo.OrderNumberSequence
);

// EF Core
builder.Property(o => o.OrderNumber)
    .HasDefaultValueSql("NEXT VALUE FOR dbo.OrderNumberSequence");

// Register sequence
modelBuilder.HasSequence<int>("OrderNumberSequence")
    .StartsAt(1000)
    .IncrementsBy(1);

计算列

-- Persisted computed column (stored on disk, indexable)
ALTER TABLE Orders ADD TotalWithTax AS (TotalAmount * 1.21) PERSISTED;

-- Non-persisted computed column (calculated on read)
ALTER TABLE Users ADD FullName AS (FirstName + ' ' + LastName);

// EF Core
builder.Property(o => o.TotalWithTax)
    .HasComputedColumnSql("[TotalAmount] * 1.21", stored: true);

builder.Property(u => u.FullName)
    .HasComputedColumnSql("[FirstName] + ' ' + [LastName]");

---

高并发模式

使用 rowversion 实现乐观并发控制

// src/{name}.domain/{Entity}/{Entity}.cs
public class Order
{
    public Guid Id { get; private set; }
    public string Status { get; private set; }

    // Concurrency token - SQL Server rowversion auto-increments on update
    public byte[] RowVersion { get; private set; }
}

// src/{name}.infrastructure/Configurations/OrderConfiguration.cs
internal sealed class OrderConfiguration : IEntityTypeConfiguration<Order>
{
    public void Configure(EntityTypeBuilder<Order> builder)
    {
        builder.ToTable("Orders");
        builder.HasKey(o => o.Id);

        // ═══════════════════════════════════════════════════════════════
        // OPTIMISTIC CONCURRENCY WITH rowversion
        // SQL Server rowversion auto-increments on every row update
        // ═══════════════════════════════════════════════════════════════
        builder.Property(o => o.RowVersion)
            .IsRowVersion();  // Maps to SQL Server rowversion type
    }
}

处理并发冲突

// src/{name}.infrastructure/Repositories/OrderRepository.cs
public async Task UpdateAsync(Order order, CancellationToken cancellationToken)
{
    try
    {
        context.Orders.Update(order);
        await context.SaveChangesAsync(cancellationToken);
    }
    catch (DbUpdateConcurrencyException ex)
    {
        var entry = ex.Entries.Single();
        var databaseValues = await entry.GetDatabaseValuesAsync(cancellationToken);

        if (databaseValues is null)
        {
            throw new EntityNotFoundException("Order was deleted by another user");
        }

        // Option 1: Client wins - overwrite database values
        // entry.OriginalValues.SetValues(databaseValues);
        // await context.SaveChangesAsync(cancellationToken);

        // Option 2: Database wins - throw and let caller retry
        throw new ConcurrencyException(
            "Order was modified by another user. Please refresh and try again.");
    }
}

使用指数退避重试（Polly）

// src/{name}.infrastructure/DependencyInjection.cs
using Polly;
using Polly.Retry;
using Microsoft.Data.SqlClient;

services.AddResiliencePipeline("database", builder =>
{
    builder.AddRetry(new RetryStrategyOptions
    {
        ShouldHandle = new PredicateBuilder()
            .Handle<DbUpdateConcurrencyException>()
            .Handle<SqlException>(ex => ex.IsTransient()),
        MaxRetryAttempts = 3,
        Delay = TimeSpan.FromMilliseconds(200),
        BackoffType = DelayBackoffType.Exponential,
        UseJitter = true,
        OnRetry = static args =>
        {
            Console.WriteLine($"Retry attempt {args.AttemptNumber} after {args.RetryDelay}");
            return default;
        }
    });
});

// SQL Server built-in retry (EF Core)
services.AddDbContext<ApplicationDbContext>(options =>
{
    options.UseSqlServer(connectionString, sqlOptions =>
    {
        sqlOptions.EnableRetryOnFailure(
            maxRetryCount: 3,
            maxRetryDelay: TimeSpan.FromSeconds(5),
            errorNumbersToAdd: null);  // Retries on transient errors
    });
});

使用 DbContext 池提升性能

// src/{name}.infrastructure/DependencyInjection.cs
services.AddDbContextPool<ApplicationDbContext>(options =>
{
    options.UseSqlServer(connectionString);
}, poolSize: 128);

// For even higher performance, use PooledDbContextFactory
services.AddPooledDbContextFactory<ApplicationDbContext>(options =>
{
    options.UseSqlServer(connectionString);
});

// Usage with PooledDbContextFactory
public class HighPerformanceQueryHandler
{
    private readonly IDbContextFactory<ApplicationDbContext> _contextFactory;

    public async Task<List<Order>> GetOrdersAsync(CancellationToken ct)
    {
        await using var context = await _contextFactory.CreateDbContextAsync(ct);

        return await context.Orders
            .AsNoTracking()
            .Where(o => o.Status == "Active")
            .ToListAsync(ct);
    }
}

连接池配置

// Optimized connection string for high concurrency
var connectionString = new SqlConnectionStringBuilder
{
    DataSource = "localhost",
    InitialCatalog = "MyDb",
    UserID = "app_user",
    Password = "secret",

    // ═══════════════════════════════════════════════════════════════
    // CONNECTION POOLING SETTINGS
    // ═══════════════════════════════════════════════════════════════
    Pooling = true,
    MinPoolSize = 10,
    MaxPoolSize = 100,

    // ═══════════════════════════════════════════════════════════════
    // TIMEOUT SETTINGS
    // ═══════════════════════════════════════════════════════════════
    ConnectTimeout = 30,
    CommandTimeout = 60,

    // ═══════════════════════════════════════════════════════════════
    // RELIABILITY SETTINGS
    // ═══════════════════════════════════════════════════════════════
    ConnectRetryCount = 3,
    ConnectRetryInterval = 10,

    // ═══════════════════════════════════════════════════════════════
    // ENCRYPTION
    // ═══════════════════════════════════════════════════════════════
    Encrypt = SqlConnectionEncryptOption.Mandatory,
    TrustServerCertificate = false
}.ConnectionString;

关键操作使用行级锁

-- UPDLOCK + ROWLOCK - Lock rows during transaction
SELECT * FROM Accounts WITH (UPDLOCK, ROWLOCK) WHERE Id = @Id;

-- READPAST - Skip locked rows (for queue processing)
SELECT TOP (@BatchSize) * 
FROM Jobs WITH (UPDLOCK, READPAST)
WHERE Status = 'Pending'
ORDER BY CreatedAt;

-- NOWAIT - Fail immediately if locked
SELECT * FROM Accounts WITH (UPDLOCK, NOWAIT) WHERE Id = @Id;

// EF Core equivalent using raw SQL
public async Task<Account?> GetForUpdateAsync(Guid id, CancellationToken ct)
{
    return await context.Accounts
        .FromSqlInterpolated(
            $"SELECT * FROM Accounts WITH (UPDLOCK, ROWLOCK) WHERE Id = {id}")
        .FirstOrDefaultAsync(ct);
}

// Queue processing with READPAST
public async Task<List<Job>> GetPendingJobsAsync(int batchSize, CancellationToken ct)
{
    return await context.Jobs
        .FromSqlInterpolated($@"
            SELECT TOP ({batchSize}) * 
            FROM Jobs WITH (UPDLOCK, READPAST)
            WHERE Status = 'Pending'
            ORDER BY CreatedAt")
        .ToListAsync(ct);
}

应用程序锁（sp_getapplock）

// SQL Server application-level locks (equivalent to PostgreSQL advisory locks)
public class AppLockService
{
    private readonly ApplicationDbContext _context;

    public async Task<bool> TryAcquireLockAsync(
        string resourceName, int timeoutMs, CancellationToken ct)
    {
        var result = await _context.Database
            .SqlQuery<int>($@"
                DECLARE @result int;
                EXEC @result = sp_getapplock 
                    @Resource = {resourceName}, 
                    @LockMode = 'Exclusive', 
                    @LockTimeout = {timeoutMs};
                SELECT @result")
            .FirstAsync(ct);

        return result >= 0;  // 0 = granted, 1 = granted after wait
    }

    public async Task ReleaseLockAsync(string resourceName, CancellationToken ct)
    {
        await _context.Database
            .ExecuteSqlAsync($"EXEC sp_releaseapplock @Resource = {resourceName}", ct);
    }
}

// Usage for distributed singleton operations
public async Task ProcessDailyReportAsync(CancellationToken ct)
{
    if (!await _lockService.TryAcquireLockAsync("DailyReport", timeoutMs: 0, ct))
    {
        _logger.LogInformation("Another instance is processing the daily report");
        return;
    }

    try
    {
        await GenerateReportAsync(ct);
    }
    finally
    {
        await _lockService.ReleaseLockAsync("DailyReport", ct);
    }
}

---

关键规则

1. 使用 PascalCase - SQL Server 标识符命名规范
2. 使用指定长度的 nvarchar - 除非确有需要，否则避免使用 nvarchar(max)
3. 始终使用 datetimeoffset - 以 UTC 存储时间，避免使用旧版 datetime
4. 为外键创建索引 - SQL Server 不会自动创建外键索引
5. GUID 主键使用 NEWSEQUENTIALID() - 避免聚集索引碎片
6. 使用 rowversion 实现并发控制 - 每次更新行时自动递增
7. 使用参数化查询 - 防止 SQL 注入
8. 监控索引碎片 - 碎片率达到 10% 时重新组织，达到 30% 时重建
9. 使用连接池 - 默认已启用，需调整池大小
10. 使用 EnableRetryOnFailure - 内置瞬时故障处理
11. 为所有约束命名 - 使用 PK_、FK_、CK_、DF_、IX_ 前缀
12. 使用时态表进行审计 - 内置历史记录跟踪

---

相关技能

• 06-dotnet-ef-core-configuration - EF Core 实体配置
• 05-dotnet-repository-pattern - 数据访问模式
• 19-dotnet-dapper-query-builder - 使用 Dapper 执行原始 SQL
• 01-dotnet-clean-architecture - 整体架构
• 25.1-dotnet-postgresql-best-practices - PostgreSQL 对应模式