dotnet-sqlserver-best-practices

.NET 向け SQL Server のベストプラクティス

概要

.NET と Entity Framework Core を使用する際の、SQL Server データベース設計、命名規則、インデックス作成、パフォーマンス最適化に関するベストプラクティスです。

クイックリファレンス

カテゴリ	ベストプラクティス
命名	テーブル／列には PascalCase を使用
主キー	NEWSEQUENTIALID() を使用する uniqueidentifier（Guid）、または int／bigint IDENTITY を使用
タイムスタンプ	UTC の datetimeoffset を使用
インデックス	外部キーと一意制約にインデックスを作成
文字列	長さを明示した nvarchar(n) を使用
JSON	JSON 関数と nvarchar(max) を使用（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
CHECK 制約	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 バイト、-20 億～+20 億
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
}

NEWID() より NEWSEQUENTIALID() が優れている理由：

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

CHECK 制約

-- 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 の同等パターン