SQL Server Advanced Design Patterns

1. Sharding

Distribute data across multiple databases or servers to improve scalability and availability.

Key Considerations

  • Shard key selection
  • Routing logic
  • Cross‑shard queries

Example: Creating Shard Map

-- Install the Shard Map Manager
CREATE DATABASE ShardMapManager;
GO
USE ShardMapManager;
GO
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max degree of parallelism', 0;
RECONFIGURE;

-- Define a range shard map
DECLARE @shardMapName sysname = N'MyRangeShardMap';
DECLARE @shardMapId int;
EXEC sp_addshardmap @name = @shardMapName, @type = N'RANGE', @shardmap_id = @shardMapId OUTPUT;

2. Temporal Tables

Track full history of data changes automatically.

Creating a System‑Versioned Table

CREATE TABLE dbo.Employee
(
    EmployeeID int PRIMARY KEY,
    Name varchar(100),
    Title varchar(100),
    Salary decimal(12,2),
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START,
    SysEndTime   datetime2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

3. Data Vault Modeling

Combine flexibility of NoSQL with the reliability of relational databases.

Core Constructs

  • Hub – business keys
  • Link – relationships
  • Satellite – descriptive attributes

Sample Hub

CREATE TABLE dbo.HubCustomer
(
    CustomerKey   UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    LoadDate      DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    RecordSource  VARCHAR(50) NOT NULL
);

4. CQRS (Command Query Responsibility Segregation)

Separate read and write models for performance and scalability.

Implementation Tips

  1. Use separate schemas or databases for commands and queries.
  2. Leverage Service Broker for async updates.
  3. Materialize views for read‑optimized data.

Sample Trigger to Sync Event Store

CREATE TRIGGER trg_Employee_Change
ON dbo.Employee
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.EventStore (EventType, Payload, CreatedAt)
    SELECT 
        CASE 
            WHEN EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) THEN 'Update'
            WHEN EXISTS (SELECT * FROM inserted) THEN 'Insert'
            ELSE 'Delete'
        END,
        (SELECT * FROM inserted FOR JSON AUTO),
        SYSDATETIME()
    FROM inserted;
END;

5. Table Partitioning

Improve query performance and manage large tables.

Creating Partition Function & Scheme

-- Partition by year on a datetime column
CREATE PARTITION FUNCTION PF_Year(datetime2) 
AS RANGE RIGHT FOR VALUES 
('2020-01-01','2021-01-01','2022-01-01','2023-01-01');

CREATE PARTITION SCHEME PS_Year 
AS PARTITION PF_Year 
ALL TO (PRIMARY);

6. Hybrid Transactional/Analytical Processing (HTAP)

Run OLTP and OLAP workloads on the same engine using columnstore indexes.

Adding a Columnstore Index

CREATE CLUSTERED COLUMNSTORE INDEX CCI_Sales
ON dbo.SalesFact;