T-SQL Server Deep-Dive Tutorials

Picking the right type saves space, speeds up queries, and prevents subtle bugs. Use NVARCHAR for Unicode text, DECIMAL(19,4) for money (not MONEY), DATETIME2 over legacy DATETIME, and ROWVERSION for optimistic concurrency.

-- ── Numeric types ─────────────────────────────
INT           -- 4 bytes, -2.1B to 2.1B
BIGINT        -- 8 bytes, ±9.2 quintillion (use when > 2.1B rows)
SMALLINT      -- 2 bytes, -32 768 to 32 767
TINYINT       -- 1 byte,  0–255
DECIMAL(19,4) -- exact fixed-point — always use for money
NUMERIC(10,2) -- alias for DECIMAL
FLOAT         -- 8-byte IEEE 754 approximate — ❌ never for money
BIT           -- 0 / 1 / NULL  (maps to bool in .NET)

-- ❌ MONEY / SMALLMONEY — proprietary, rounding surprises
-- ✅ DECIMAL(19,4) is portable, exact, and index-friendly

-- ── String types ───────────────────────────────
VARCHAR(200)  -- ANSI, 1 byte/char, max 8 000
NVARCHAR(200) -- Unicode UTF-16, 2 bytes/char, max 4 000
NVARCHAR(MAX) -- up to 2 GB — avoid in indexes and key columns
CHAR(10)      -- fixed-width ANSI, pads with spaces
NCHAR(10)     -- fixed-width Unicode

-- Rule of thumb:
--   • User-facing text (names, titles) → NVARCHAR
--   • Internal codes / flags (ISO codes, status) → VARCHAR or CHAR
--   • Never store numbers / dates as strings

-- ── Date / time ────────────────────────────────
DATE           -- date only              (3 bytes)
TIME(7)        -- time only              (5 bytes)
DATETIME2(7)   -- date + time, high precision (6–8 bytes) ✅
DATETIMEOFFSET -- date + time + UTC offset  (10 bytes)
-- ❌ DATETIME   — only 1/300 s precision, ends 9999 — use DATETIME2
-- ❌ SMALLDATETIME — ends 2079 — avoid in new schemas

-- ── Optimistic concurrency ─────────────────────
ROWVERSION    -- 8-byte, auto-updated on every write
              -- detect concurrent changes without locking:
              --   WHERE RowVer = @capturedVersion

-- ── Other ──────────────────────────────────────
UNIQUEIDENTIFIER -- GUID (16 bytes) — use NEWSEQUENTIALID()
                 -- not NEWID() to reduce page fragmentation
VARBINARY(MAX)   -- binary blobs (prefer file storage for > 1 MB)
XML              -- native XML with XQuery support

Always specify a schema (e.g. dbo), use IDENTITY for surrogate keys, and add constraints at table-creation time — not as an afterthought.

-- Create a table with common patterns
CREATE TABLE dbo.Products (
    ProductId   INT            IDENTITY(1,1) NOT NULL,
    Name        NVARCHAR(200)  NOT NULL,
    Price       DECIMAL(10,2)  NOT NULL  CONSTRAINT CK_Price_Positive CHECK (Price >= 0),
    CategoryId  INT            NOT NULL,
    IsActive    BIT            NOT NULL  DEFAULT 1,
    CreatedAt   DATETIME2(7)   NOT NULL  DEFAULT SYSUTCDATETIME(),
    UpdatedAt   DATETIME2(7)   NULL,
    CONSTRAINT PK_Products PRIMARY KEY CLUSTERED (ProductId),
    CONSTRAINT FK_Products_Categories
        FOREIGN KEY (CategoryId) REFERENCES dbo.Categories (CategoryId)
            ON DELETE RESTRICT
            ON UPDATE CASCADE
);

-- Add a column safely (non-breaking)
ALTER TABLE dbo.Products
    ADD Description NVARCHAR(MAX) NULL;

-- Rename a column (use sp_rename)
EXEC sp_rename 'dbo.Products.IsActive', 'IsEnabled', 'COLUMN';

-- Drop a table (check existence first)
IF OBJECT_ID('dbo.Products', 'U') IS NOT NULL
    DROP TABLE dbo.Products;

OUTPUT lets you capture affected rows in a single statement. MERGE handles upsert atomically but watch for edge cases with multiple source matches.

-- INSERT — multi-row, returns new IDs
INSERT INTO dbo.Products (Name, Price, CategoryId)
OUTPUT INSERTED.ProductId, INSERTED.Name
VALUES
    (N'Coffee', 3.50, 1),
    (N'Latte',  4.50, 1),
    (N'Muffin', 2.25, 2);

-- UPDATE with OUTPUT
UPDATE dbo.Products
SET    Price     = Price * 1.10,
       UpdatedAt = SYSUTCDATETIME()
OUTPUT DELETED.Price AS OldPrice,
       INSERTED.Price AS NewPrice,
       INSERTED.ProductId
WHERE  CategoryId = 1
  AND  IsEnabled  = 1;

-- DELETE — remove stale records
DELETE FROM dbo.Products
WHERE  IsEnabled = 0
  AND  UpdatedAt < DATEADD(DAY, -90, SYSUTCDATETIME());

-- MERGE — upsert from staging table
MERGE dbo.Products AS target
USING dbo.ProductsStaging AS src
  ON  target.ProductId = src.ProductId
WHEN MATCHED THEN
    UPDATE SET target.Price = src.Price,
               target.UpdatedAt = SYSUTCDATETIME()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Name, Price, CategoryId)
    VALUES (src.Name, src.Price, src.CategoryId)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

Good schema design prevents data anomalies before they happen. Aim for 3NF in OLTP schemas, use surrogate INT IDENTITY PKs by default, and always enforce relationships with FK constraints.

-- ── 1NF: Atomic values — no repeating groups ─
-- ❌ Comma-list in a single column is un-queryable
CREATE TABLE dbo.Articles (
    Id    INT PRIMARY KEY,
    Title NVARCHAR(200),
    Tags  VARCHAR(500)   -- "sql,tsql,database" — breaks 1NF
);

-- ✅ Separate entity table (also enables indexing)
CREATE TABLE dbo.ArticleTags (
    ArticleId INT         NOT NULL REFERENCES dbo.Articles(Id),
    Tag       VARCHAR(50) NOT NULL,
    CONSTRAINT PK_ArticleTags PRIMARY KEY (ArticleId, Tag)
);

-- ── 3NF: Remove transitive dependencies ───────
-- ❌ City depends on ZipCode, not on CustomerId
CREATE TABLE dbo.Customers_Bad (
    CustomerId INT PRIMARY KEY,
    ZipCode    CHAR(5),
    City       VARCHAR(100)  -- transitive: ZipCode → City
);

-- ✅ Extract the dependency to its own table
CREATE TABLE dbo.ZipCodes (
    ZipCode CHAR(5)       NOT NULL PRIMARY KEY,
    City    NVARCHAR(100) NOT NULL
);
CREATE TABLE dbo.Customers (
    CustomerId INT    NOT NULL IDENTITY PRIMARY KEY,
    ZipCode    CHAR(5)        REFERENCES dbo.ZipCodes(ZipCode)
);

-- ── Relationship patterns ──────────────────────
-- 1:N  → FK on the "many" side
-- 1:1  → FK + UNIQUE constraint on child table
-- M:N  → junction / bridge table

-- M:N junction table (Products ↔ Categories)
CREATE TABLE dbo.ProductCategories (
    ProductId  INT NOT NULL REFERENCES dbo.Products(ProductId),
    CategoryId INT NOT NULL REFERENCES dbo.Categories(CategoryId),
    SortOrder  TINYINT NOT NULL DEFAULT 0,
    CONSTRAINT PK_ProductCategories PRIMARY KEY (ProductId, CategoryId)
);

-- ── PK strategy comparison ─────────────────────
-- ✅ INT IDENTITY  — compact (4 B), sequential, fast inserts (default choice)
-- ✅ BIGINT IDENTITY — when you expect > 2.1 B rows
-- ⚠  UNIQUEIDENTIFIER with NEWSEQUENTIALID() — for distributed / replication
--    (NEWID() causes page fragmentation — avoid as clustered PK)
-- ❌ Natural keys (email, SSN) — they change and cause cascade updates

-- ── Naming conventions (consistency beats style) ─
-- Tables   : PascalCase, singular  → dbo.Customer, dbo.OrderLine
-- Columns  : PascalCase            → CustomerId, CreatedAt
-- PKs      : PK_TableName
-- FKs      : FK_ChildTable_ParentTable
-- Indexes  : IX_TableName_Columns, UX_ for unique
-- Procs    : usp_VerbNoun          → usp_CreateOrder, usp_GetCustomer
-- Functions: fn_VerbNoun           → fn_FullName, fn_CustomerOrders

Always use OFFSET … FETCH for pagination — never SELECT TOP with row numbering tricks. Sargable predicates (those that can use an index) keep queries fast.

-- Basic projection and filter
SELECT p.ProductId,
       p.Name,
       p.Price,
       c.Name AS CategoryName
FROM   dbo.Products  AS p
JOIN   dbo.Categories AS c ON c.CategoryId = p.CategoryId
WHERE  p.IsEnabled = 1
  AND  p.Price BETWEEN 1.00 AND 10.00
ORDER  BY p.Price DESC;

-- ── Sargable vs non-sargable ──────────────────
-- ✅ Sargable — index on CreatedAt can be used
WHERE CreatedAt >= '2025-01-01'
  AND CreatedAt <  '2026-01-01'

-- ❌ Non-sargable — function wraps the column
WHERE YEAR(CreatedAt) = 2025

-- ── Keyset pagination (fast, no OFFSET drift) ─
SELECT TOP 20 ProductId, Name, Price
FROM   dbo.Products
WHERE  IsEnabled = 1
  AND  ProductId > @lastId   -- cursor from previous page
ORDER  BY ProductId;

-- ── OFFSET/FETCH (standard pagination) ────────
SELECT ProductId, Name, Price
FROM   dbo.Products
WHERE  IsEnabled = 1
ORDER  BY CreatedAt DESC
OFFSET  (@page - 1) * @pageSize ROWS
FETCH NEXT @pageSize ROWS ONLY;

INNER JOIN returns only matching rows. LEFT JOIN keeps all rows from the left table. Use CROSS APPLY when you need a correlated subquery per row.

-- INNER JOIN — only matched rows
SELECT o.OrderId, c.Name AS Customer, o.Total
FROM   dbo.Orders    AS o
INNER JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId;

-- LEFT JOIN — all orders including those with no invoice
SELECT o.OrderId, i.InvoiceNumber
FROM   dbo.Orders   AS o
LEFT  JOIN dbo.Invoices AS i ON i.OrderId = o.OrderId
WHERE  i.InvoiceId IS NULL;  -- find orders WITHOUT an invoice

-- FULL OUTER JOIN — all rows from both sides
SELECT COALESCE(a.Id, b.Id) AS Id,
       a.Name AS NameA,
       b.Name AS NameB
FROM   dbo.TableA AS a
FULL  OUTER JOIN dbo.TableB AS b ON b.Id = a.Id;

-- CROSS APPLY — correlated function per row
SELECT c.CustomerId,
       c.Name,
       recent.OrderId,
       recent.Total
FROM   dbo.Customers AS c
CROSS APPLY (
    SELECT TOP 1 OrderId, Total
    FROM   dbo.Orders
    WHERE  CustomerId = c.CustomerId
    ORDER  BY OrderDate DESC
) AS recent;

-- SELF JOIN — org-chart / hierarchy
SELECT e.Name AS Employee,
       m.Name AS Manager
FROM   dbo.Employees AS e
LEFT  JOIN dbo.Employees AS m ON m.EmployeeId = e.ManagerId;

Prefer EXISTS over IN (SELECT …) for correlated lookups — it short-circuits on first match. Rewrite scalar subqueries in SELECT as CROSS APPLY when they execute per row.

-- ── EXISTS — short-circuits on first match ────
SELECT c.CustomerId, c.Name
FROM   dbo.Customers AS c
WHERE  EXISTS (
    SELECT 1
    FROM   dbo.Orders AS o
    WHERE  o.CustomerId = c.CustomerId
      AND  o.Total > 1000
);

-- ── NOT EXISTS — anti-join pattern ────────────
SELECT p.ProductId, p.Name
FROM   dbo.Products AS p
WHERE  NOT EXISTS (
    SELECT 1 FROM dbo.OrderLines AS ol
    WHERE  ol.ProductId = p.ProductId
);

-- ── IN vs JOIN (equivalent, optimizer may unify) ─
-- IN — readable for small static lists
SELECT * FROM dbo.Products
WHERE CategoryId IN (1, 3, 5);

-- JOIN — better when the list comes from a table
SELECT p.*
FROM   dbo.Products AS p
INNER JOIN (VALUES (1),(3),(5)) AS ids(Id) ON ids.Id = p.CategoryId;

-- ── Derived table in FROM ─────────────────────
SELECT dept, AVG(salary) AS AvgSalary
FROM (
    SELECT e.DeptId AS dept, e.Salary AS salary
    FROM   dbo.Employees AS e
    WHERE  e.IsActive = 1
) AS active_emps
GROUP BY dept
HAVING AVG(salary) > 50000;

WHERE filters before aggregation; HAVING filters after. Use GROUPING SETS to produce multiple aggregation levels in one pass.

-- Basic aggregation
SELECT c.Name              AS Category,
       COUNT(*)            AS TotalProducts,
       COUNT(p.Price)      AS PricedProducts,    -- ignores NULLs
       SUM(p.Price)        AS TotalValue,
       AVG(p.Price)        AS AvgPrice,
       MIN(p.Price)        AS Cheapest,
       MAX(p.Price)        AS MostExpensive,
       STDEV(p.Price)      AS PriceStdDev
FROM   dbo.Products   AS p
JOIN   dbo.Categories AS c ON c.CategoryId = p.CategoryId
WHERE  p.IsEnabled = 1
GROUP  BY c.Name
HAVING COUNT(*) >= 5         -- only categories with 5+ products
ORDER  BY TotalValue DESC;

-- ── GROUPING SETS — multiple rollups at once ──
SELECT CategoryId,
       YEAR(OrderDate) AS OrderYear,
       SUM(Total)      AS Revenue
FROM   dbo.Orders
GROUP  BY GROUPING SETS (
    (CategoryId, YEAR(OrderDate)),  -- by category + year
    (CategoryId),                    -- by category total
    ()                               -- grand total
);

-- ── ROLLUP shorthand ─────────────────────────
SELECT Region, Country, SUM(Sales)
FROM   dbo.SalesData
GROUP  BY ROLLUP (Region, Country);

CTEs improve readability by naming intermediate results. Recursive CTEs are the standard way to walk hierarchies (org charts, bills of materials) in SQL Server.

-- ── Single CTE ────────────────────────────────
WITH TopCustomers AS (
    SELECT   CustomerId,
             SUM(Total) AS LifetimeValue
    FROM     dbo.Orders
    WHERE    Status = 'Completed'
    GROUP BY CustomerId
    HAVING   SUM(Total) > 10000
)
SELECT c.Name,
       tc.LifetimeValue
FROM   dbo.Customers AS c
JOIN   TopCustomers  AS tc ON tc.CustomerId = c.CustomerId
ORDER  BY tc.LifetimeValue DESC;

-- ── Multiple CTEs (chained) ───────────────────
WITH
MonthlySales AS (
    SELECT YEAR(OrderDate)  AS yr,
           MONTH(OrderDate) AS mo,
           SUM(Total)       AS Revenue
    FROM   dbo.Orders
    GROUP  BY YEAR(OrderDate), MONTH(OrderDate)
),
RunningTotal AS (
    SELECT yr, mo, Revenue,
           SUM(Revenue) OVER (PARTITION BY yr ORDER BY mo) AS YTD
    FROM   MonthlySales
)
SELECT * FROM RunningTotal ORDER BY yr, mo;

-- ── Recursive CTE — org chart ─────────────────
WITH OrgChart AS (
    -- anchor: top-level employees (no manager)
    SELECT EmployeeId, Name, ManagerId, 0 AS Level
    FROM   dbo.Employees
    WHERE  ManagerId IS NULL

    UNION ALL

    -- recursive: employees whose manager is already in CTE
    SELECT e.EmployeeId, e.Name, e.ManagerId, oc.Level + 1
    FROM   dbo.Employees AS e
    JOIN   OrgChart      AS oc ON oc.EmployeeId = e.ManagerId
)
SELECT REPLICATE('  ', Level) + Name AS Hierarchy, Level
FROM   OrgChart
ORDER  BY Level, Name
OPTION (MAXRECURSION 100);  -- guard against infinite loops

Use PIVOT when column names are known at compile time. For dynamic columns, build the query with STRING_AGG and EXEC sp_executesql. Conditional aggregation with CASE is often simpler and equally fast.

-- ── Static PIVOT ──────────────────────────────
SELECT *
FROM (
    SELECT ProductId, YEAR(OrderDate) AS OrderYear, Total
    FROM   dbo.Orders
) AS src
PIVOT (
    SUM(Total) FOR OrderYear IN ([2023],[2024],[2025])
) AS pvt;

-- ── Conditional aggregation (more flexible) ───
SELECT ProductId,
       SUM(CASE WHEN YEAR(OrderDate) = 2023 THEN Total ELSE 0 END) AS Rev2023,
       SUM(CASE WHEN YEAR(OrderDate) = 2024 THEN Total ELSE 0 END) AS Rev2024,
       SUM(CASE WHEN YEAR(OrderDate) = 2025 THEN Total ELSE 0 END) AS Rev2025
FROM   dbo.Orders
GROUP  BY ProductId;

-- ── Dynamic PIVOT ─────────────────────────────
DECLARE @cols   NVARCHAR(MAX),
        @sql    NVARCHAR(MAX);

SELECT @cols = STRING_AGG(QUOTENAME(YEAR(OrderDate)), ',')
               WITHIN GROUP (ORDER BY YEAR(OrderDate))
FROM  (SELECT DISTINCT OrderDate FROM dbo.Orders) AS d;

SET @sql = N'
SELECT * FROM (
    SELECT ProductId, YEAR(OrderDate) AS yr, Total FROM dbo.Orders
) AS src
PIVOT (SUM(Total) FOR yr IN (' + @cols + N')) AS pvt;';

EXEC sp_executesql @sql;

Always use SET NOCOUNT ON to suppress row-count noise. Validate inputs early, use TRY…CATCH for error handling, and return meaningful RAISERROR messages.

CREATE OR ALTER PROCEDURE dbo.usp_CreateOrder
    @CustomerId  INT,
    @Lines       dbo.OrderLineType READONLY,  -- table-valued parameter
    @OrderId     INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;  -- auto-rollback on any error

    -- ── Validate ──────────────────────────────
    IF NOT EXISTS (SELECT 1 FROM dbo.Customers WHERE CustomerId = @CustomerId)
        THROW 50001, 'Customer not found.', 1;

    IF NOT EXISTS (SELECT 1 FROM @Lines)
        THROW 50002, 'Order must have at least one line.', 1;

    -- ── Execute ───────────────────────────────
    BEGIN TRY
        BEGIN TRANSACTION;

        INSERT INTO dbo.Orders (CustomerId, OrderDate, Status)
        VALUES (@CustomerId, SYSUTCDATETIME(), 'Pending');

        SET @OrderId = SCOPE_IDENTITY();

        INSERT INTO dbo.OrderLines (OrderId, ProductId, Qty, UnitPrice)
        SELECT @OrderId, ProductId, Qty, UnitPrice
        FROM   @Lines;

        -- Update stock
        UPDATE p
        SET    p.StockQty = p.StockQty - l.Qty
        FROM   dbo.Products   AS p
        JOIN   @Lines         AS l ON l.ProductId = p.ProductId;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

        DECLARE @msg  NVARCHAR(2048) = ERROR_MESSAGE(),
                @sev  INT            = ERROR_SEVERITY(),
                @st   INT            = ERROR_STATE();
        RAISERROR(@msg, @sev, @st);
    END CATCH;
END;
GO

-- Calling the procedure
DECLARE @newId INT;
EXEC dbo.usp_CreateOrder
    @CustomerId = 42,
    @Lines      = @myLinesVar,
    @OrderId    = @newId OUTPUT;
SELECT @newId AS NewOrderId;

Prefer inline table-valued functions (iTVF) over scalar UDFs — the optimizer can expand iTVFs into the calling query, whereas scalar UDFs often prevent parallelism and row-mode execution.

-- ── Scalar UDF (use sparingly — blocks parallelism) ─
CREATE OR ALTER FUNCTION dbo.fn_FullName
    (@First NVARCHAR(100), @Last NVARCHAR(100))
RETURNS NVARCHAR(201)
AS BEGIN
    RETURN LTRIM(RTRIM(@First + ' ' + @Last));
END;
GO

-- ── Inline TVF — optimizer can inline this ────
CREATE OR ALTER FUNCTION dbo.fn_CustomerOrders
    (@CustomerId INT, @FromDate DATE)
RETURNS TABLE
AS
RETURN (
    SELECT o.OrderId,
           o.OrderDate,
           o.Total,
           o.Status
    FROM   dbo.Orders AS o
    WHERE  o.CustomerId = @CustomerId
      AND  o.OrderDate >= @FromDate
);
GO

-- Usage — join like a table
SELECT c.Name, fo.*
FROM   dbo.Customers AS c
CROSS APPLY dbo.fn_CustomerOrders(c.CustomerId, '2025-01-01') AS fo
WHERE  c.IsActive = 1;

-- ── Multi-statement TVF (when logic is complex) ─
CREATE OR ALTER FUNCTION dbo.fn_ProductHierarchy
    (@RootId INT)
RETURNS @result TABLE (
    ProductId INT, ParentId INT, Depth INT
)
AS BEGIN
    WITH cte AS (
        SELECT ProductId, ParentId, 0 AS Depth
        FROM   dbo.Products WHERE ProductId = @RootId
        UNION ALL
        SELECT p.ProductId, p.ParentId, c.Depth + 1
        FROM   dbo.Products AS p JOIN cte AS c ON c.ProductId = p.ParentId
    )
    INSERT @result SELECT * FROM cte;
    RETURN;
END;
GO

TVPs let you pass a set of rows from your application to a stored procedure in a single round-trip — vastly more efficient than row-by-row inserts or comma-delimited string parsing.

-- 1. Define the type (once per database)
CREATE TYPE dbo.OrderLineType AS TABLE (
    ProductId  INT            NOT NULL,
    Qty        INT            NOT NULL,
    UnitPrice  DECIMAL(10,2)  NOT NULL
);
GO

-- 2. Use it in a procedure
CREATE OR ALTER PROCEDURE dbo.usp_BulkInsertOrderLines
    @OrderId INT,
    @Lines   dbo.OrderLineType READONLY
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.OrderLines (OrderId, ProductId, Qty, UnitPrice)
    SELECT @OrderId, ProductId, Qty, UnitPrice
    FROM   @Lines;
END;
GO

-- 3. Bulk insert from .NET (C# / Dapper)
-- var lines = new DataTable();
-- lines.Columns.Add("ProductId", typeof(int));
-- lines.Columns.Add("Qty",       typeof(int));
-- lines.Columns.Add("UnitPrice", typeof(decimal));
-- lines.Rows.Add(1, 3, 9.99m);
-- conn.Execute("dbo.usp_BulkInsertOrderLines",
--     new { OrderId = 5, Lines = lines.AsTableValuedParameter("dbo.OrderLineType") },
--     commandType: CommandType.StoredProcedure);

-- 4. BULK INSERT from a CSV file
BULK INSERT dbo.Products
FROM 'C:\data\products.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR   = '\n',
    FIRSTROW        = 2,           -- skip header
    TABLOCK,                        -- table lock for speed
    BATCHSIZE       = 5000
);

SET XACT_ABORT ON automatically rolls back the entire transaction on any error — pair it with TRY…CATCH so you control the message. Always check @@TRANCOUNT before rolling back.

-- ── Explicit transaction with error handling ──
BEGIN TRY
    SET XACT_ABORT ON;
    BEGIN TRANSACTION;

        -- Debit account
        UPDATE dbo.Accounts
        SET    Balance = Balance - @amount
        WHERE  AccountId = @fromId;

        IF (SELECT Balance FROM dbo.Accounts WHERE AccountId = @fromId) < 0
            THROW 50010, 'Insufficient funds.', 1;

        -- Credit account
        UPDATE dbo.Accounts
        SET    Balance = Balance + @amount
        WHERE  AccountId = @toId;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;  -- re-throw to caller
END CATCH;

-- ── SAVEPOINT — partial rollback ─────────────
BEGIN TRANSACTION;
    INSERT INTO dbo.AuditLog (Msg) VALUES ('Step 1');
    SAVE TRANSACTION after_step1;

    BEGIN TRY
        INSERT INTO dbo.RiskyTable (Id) VALUES (NULL); -- may fail
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION after_step1;  -- undo risky step only
    END CATCH;

COMMIT TRANSACTION;  -- commits step 1 regardless

Enable Read Committed Snapshot Isolation (RCSI) at the database level — it eliminates most reader/writer blocking with zero application code changes and is the default in Azure SQL.

-- ── Isolation levels (ascending strictness) ───
-- READ UNCOMMITTED — dirty reads allowed (avoid in production)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- READ COMMITTED (default) — no dirty reads, phantom reads possible
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- REPEATABLE READ — data read can't change mid-transaction
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- SERIALIZABLE — no phantom rows; full isolation (highest locks)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- SNAPSHOT — statement-level snapshot via tempdb versioning
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

-- ── Enable RCSI (recommended for OLTP) ────────
-- Run once per database (brief lock, then instant)
ALTER DATABASE [YourDB]
    SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

-- After RCSI is on, normal READ COMMITTED queries
-- read a row version instead of taking a shared lock
SELECT * FROM dbo.Orders;  -- never blocked by writers

-- ── NOLOCK hint — use with caution ────────────
-- Equivalent to READ UNCOMMITTED; may return phantom rows
SELECT * FROM dbo.Orders WITH (NOLOCK);  -- ❌ prefer RCSI instead

-- ── UPDLOCK — prevent lost updates ────────────
BEGIN TRANSACTION;
    SELECT @stock = StockQty
    FROM   dbo.Products WITH (UPDLOCK)  -- hold U lock, not S lock
    WHERE  ProductId = @id;

    IF @stock >= @qty
        UPDATE dbo.Products SET StockQty = StockQty - @qty
        WHERE  ProductId = @id;
COMMIT;

Most deadlocks are caused by inconsistent lock-acquisition order. Fix them by accessing tables in the same order across all transactions and keeping transactions short.

-- ── Deadlock trace via Extended Events ────────
-- (run in SSMS once to set up the session)
CREATE EVENT SESSION [DeadlockTrace] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE = ON);
GO
ALTER EVENT SESSION [DeadlockTrace] ON SERVER STATE = START;

-- Read the captured deadlock graphs
SELECT  CAST(target_data AS XML).query(
            'ring_buffer/RingBufferTarget/event/data/value/deadlock'
        ) AS DeadlockGraph
FROM    sys.dm_xe_session_targets   AS t
JOIN    sys.dm_xe_sessions          AS s ON s.address = t.event_session_address
WHERE   s.name = 'DeadlockTrace'
  AND   t.target_name = 'ring_buffer';

-- ── Prevention checklist ─────────────────────
-- 1. Always access tables in the same order (A then B, never B then A)
-- 2. Keep transactions as short as possible
-- 3. Use RCSI to eliminate reader/writer deadlocks
-- 4. Index FK columns so SQL Server doesn't table-scan on cascade
-- 5. Mark the low-priority transaction as deadlock victim:
SET DEADLOCK_PRIORITY LOW;  -- this session loses the race

Every table should have a clustered index (usually the PK). Add non-clustered indexes on columns that appear in WHERE, JOIN, or ORDER BY. Use INCLUDE to cover the SELECT columns without widening the key.

-- ── Clustered index — defines physical row order ─
CREATE CLUSTERED INDEX CIX_Orders_OrderDate
    ON dbo.Orders (OrderDate DESC);

-- ── Non-clustered covering index ──────────────
-- key columns go in WHERE / JOIN / ORDER BY
-- INCLUDE columns go in SELECT
CREATE NONCLUSTERED INDEX IX_Orders_Customer_Status
    ON dbo.Orders (CustomerId, Status)
    INCLUDE (OrderDate, Total)
    WHERE Status IN ('Pending', 'Processing');  -- filtered index

-- ── Unique index ──────────────────────────────
CREATE UNIQUE NONCLUSTERED INDEX UX_Customers_Email
    ON dbo.Customers (Email)
    WHERE Email IS NOT NULL;

-- ── Check index usage stats ───────────────────
SELECT  OBJECT_NAME(i.object_id)          AS TableName,
        i.name                            AS IndexName,
        us.user_seeks + us.user_scans     AS TotalReads,
        us.user_updates                   AS TotalWrites,
        i.fill_factor
FROM    sys.indexes               AS i
JOIN    sys.dm_db_index_usage_stats AS us
        ON us.object_id = i.object_id
        AND us.index_id = i.index_id
WHERE   OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
  AND   us.database_id = DB_ID()
ORDER   BY TotalReads DESC;

-- ── Missing index suggestions ─────────────────
SELECT  mid.statement,
        migs.avg_user_impact,
        mid.equality_columns,
        mid.include_columns
FROM    sys.dm_db_missing_index_details  AS mid
JOIN    sys.dm_db_missing_index_groups   AS mig  ON mig.index_handle  = mid.index_handle
JOIN    sys.dm_db_missing_index_group_stats AS migs ON migs.group_handle = mig.index_group_handle
WHERE   mid.database_id = DB_ID()
ORDER   BY migs.avg_user_impact DESC;

Always tune with SET STATISTICS IO, TIME ON — raw elapsed time is misleading. An index seek is your target; a table scan or a fat key lookup is the signal to add an index.

-- ── Enable I/O and time statistics ───────────
SET STATISTICS IO  ON;
SET STATISTICS TIME ON;

-- Run your query here, then check Messages tab:
-- "Table 'Orders'. Scan count 1, logical reads 12"
SELECT * FROM dbo.Orders WHERE CustomerId = 5;

SET STATISTICS IO  OFF;
SET STATISTICS TIME OFF;

-- ── Show estimated plan (no execution) ────────
-- In SSMS: Ctrl+L  or use:
SET SHOWPLAN_XML ON;
GO
SELECT * FROM dbo.Orders WHERE CustomerId = 5;
GO
SET SHOWPLAN_XML OFF;
GO

-- ── Check actual cached plans ─────────────────
SELECT  qs.execution_count,
        qs.total_logical_reads / qs.execution_count AS AvgLogicalReads,
        qs.total_elapsed_time  / qs.execution_count / 1000 AS AvgMs,
        SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
            (CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
             ELSE qs.statement_end_offset END - qs.statement_start_offset)/2 + 1
        ) AS QueryText
FROM    sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER   BY AvgLogicalReads DESC;

Parameter sniffing causes a plan compiled for one parameter value to perform poorly for another. The fix is rarely a hint — usually it's an index, a statistics update, or restructured code.

-- ── Update statistics (do this after large loads) ─
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;
UPDATE STATISTICS dbo.Orders (IX_Orders_Customer_Status) WITH FULLSCAN;

-- ── Check auto-update stats settings ──────────
SELECT name, is_auto_update_stats_on, is_auto_create_stats_on
FROM   sys.databases WHERE name = DB_NAME();

-- ── Parameter sniffing workaround 1: OPTIMIZE FOR ─
SELECT * FROM dbo.Orders
WHERE  CustomerId = @id
OPTION (OPTIMIZE FOR (@id = 1));  -- compile plan for customer 1

-- ── Parameter sniffing workaround 2: RECOMPILE ──
-- (forces fresh plan every execution — only use if plans vary a lot)
EXEC dbo.usp_GetOrders @CustomerId = 5 WITH RECOMPILE;

-- Or inside the proc:
CREATE PROCEDURE dbo.usp_GetOrders @CustomerId INT
AS
BEGIN
    SELECT * FROM dbo.Orders WHERE CustomerId = @CustomerId
    OPTION (RECOMPILE);  -- recompile this statement only
END;

-- ── Force a specific index (last resort) ──────
SELECT * FROM dbo.Orders WITH (INDEX(IX_Orders_Customer_Status))
WHERE  CustomerId = 5;

-- ── Disable parallelism for one query ─────────
SELECT * FROM dbo.LargeTable
OPTION (MAXDOP 1);

Window functions operate over a frame of rows without collapsing them like GROUP BY. They enable running totals, ranking, and gap detection in a single pass.

-- ── Ranking functions ─────────────────────────
SELECT
    ProductId,
    CategoryId,
    Price,
    ROW_NUMBER() OVER (PARTITION BY CategoryId ORDER BY Price DESC) AS RowNum,
    RANK()       OVER (PARTITION BY CategoryId ORDER BY Price DESC) AS Rank,
    DENSE_RANK() OVER (PARTITION BY CategoryId ORDER BY Price DESC) AS DenseRank,
    NTILE(4)     OVER (ORDER BY Price)                              AS Quartile
FROM dbo.Products;

-- ── Running total and moving average ──────────
SELECT
    OrderDate,
    Total,
    SUM(Total) OVER (ORDER BY OrderDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal,
    AVG(Total) OVER (ORDER BY OrderDate
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)         AS Rolling7DayAvg
FROM dbo.Orders
WHERE CustomerId = 1;

-- ── LAG / LEAD — compare with adjacent rows ───
SELECT
    OrderDate,
    Total,
    LAG(Total,  1, 0) OVER (ORDER BY OrderDate) AS PrevTotal,
    LEAD(Total, 1, 0) OVER (ORDER BY OrderDate) AS NextTotal,
    Total - LAG(Total, 1, 0) OVER (ORDER BY OrderDate) AS Diff
FROM dbo.Orders;

-- ── Top N per group (classic use case) ────────
WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY CategoryId ORDER BY Price DESC) AS rn
    FROM   dbo.Products
    WHERE  IsEnabled = 1
)
SELECT * FROM ranked WHERE rn <= 3;  -- top 3 per category

Temporal tables (SQL Server 2016+) automatically track the full history of every row change with zero application code. Use them for audit trails, point-in-time reporting, and regulatory compliance.

-- ── Create a temporal table ───────────────────
CREATE TABLE dbo.Prices (
    PriceId     INT            IDENTITY NOT NULL,
    ProductId   INT            NOT NULL,
    Amount      DECIMAL(10,2)  NOT NULL,
    ValidFrom   DATETIME2(7) GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo     DATETIME2(7) GENERATED ALWAYS AS ROW END   NOT NULL,
    CONSTRAINT PK_Prices PRIMARY KEY (PriceId),
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.PricesHistory));

-- Normal DML — SQL Server handles history automatically
UPDATE dbo.Prices SET Amount = 4.99 WHERE PriceId = 1;
UPDATE dbo.Prices SET Amount = 5.49 WHERE PriceId = 1;

-- ── Query as of a specific point in time ──────
SELECT * FROM dbo.Prices
FOR SYSTEM_TIME AS OF '2025-06-01 12:00:00';

-- ── Query a range of changes ──────────────────
SELECT * FROM dbo.Prices
FOR SYSTEM_TIME BETWEEN '2025-01-01' AND '2025-12-31'
WHERE ProductId = 1
ORDER BY ValidFrom;

-- ── All versions including history ────────────
SELECT * FROM dbo.Prices
FOR SYSTEM_TIME ALL
WHERE ProductId = 1;

-- ── Turn off versioning (e.g. for bulk rebuild) ─
ALTER TABLE dbo.Prices SET (SYSTEM_VERSIONING = OFF);
-- do maintenance here...
ALTER TABLE dbo.Prices SET (SYSTEM_VERSIONING = ON
    (HISTORY_TABLE = dbo.PricesHistory));

SQL Server 2016+ has first-class JSON support for shredding API payloads and generating JSON output. Use sp_executesql with parameters for dynamic SQL — never concatenate user input directly.

-- ── Parse JSON from a column ──────────────────
SELECT
    JSON_VALUE(Payload, '$.orderId')            AS OrderId,
    JSON_VALUE(Payload, '$.customer.name')      AS CustomerName,
    JSON_QUERY(Payload, '$.lines')              AS LinesJson
FROM dbo.WebhookEvents;

-- ── Shred JSON array with OPENJSON ────────────
DECLARE @json NVARCHAR(MAX) = N'
[
  {"id": 1, "name": "Coffee", "price": 3.50},
  {"id": 2, "name": "Latte",  "price": 4.50}
]';

SELECT id, name, price
FROM OPENJSON(@json)
WITH (
    id    INT           '$.id',
    name  NVARCHAR(100) '$.name',
    price DECIMAL(10,2) '$.price'
);

-- ── Generate JSON output ───────────────────────
SELECT ProductId, Name, Price
FROM   dbo.Products
WHERE  IsEnabled = 1
FOR JSON PATH, ROOT('products');

-- ── Safe dynamic SQL — always parameterise ────
DECLARE @tableName  SYSNAME = N'dbo.Orders';
DECLARE @customerId INT     = 42;
DECLARE @sql        NVARCHAR(MAX);

SET @sql = N'SELECT * FROM ' + QUOTENAME(@tableName)
         + N' WHERE CustomerId = @cid ORDER BY OrderDate DESC;';

EXEC sp_executesql @sql,
     N'@cid INT',  -- parameter definition
     @cid = @customerId;

-- ❌ NEVER do string concatenation with user input:
-- SET @sql = 'SELECT * FROM Orders WHERE Name = ''' + @userInput + '''';
-- EXEC(@sql);  -- SQL injection risk!