Back to handbooks index

Microsoft SQL Server: Intermediate to Advanced Developer Handbook

A SQL Server engineering guide focused on APPLY operators, analytical windowing, recursive CTEs, native pivoting, stored procedures, function design, and safe dynamic SQL.

SQL Server 2022+ Inventory & Sales APPLY · PIVOT Stored Procedures · iTVFs April 2026
ℹ
Focus: this handbook assumes the basics are already comfortable. The goal here is to write higher-leverage T-SQL that works with the optimizer instead of against it.

Module 1: Advanced Joins & Set Operators

CROSS JOIN

A cross join returns every combination. It is useful when you intentionally want a matrix, such as all warehouses crossed with all shipping priorities.

SELECT
    w.WarehouseName,
    p.PriorityName
FROM (VALUES (N'East Hub'), (N'Central Hub'), (N'West Hub')) AS w(WarehouseName)
CROSS JOIN (VALUES (N'Standard'), (N'Expedited')) AS p(PriorityName)
ORDER BY w.WarehouseName, p.PriorityName;

CROSS APPLY and OUTER APPLY

These are SQL Server's lateral join operators. They let the right side reference columns from the left side. CROSS APPLY keeps only matching results. OUTER APPLY preserves the left row even when the right side returns nothing.

CREATE OR ALTER FUNCTION Sales.ufnTopOrderLines (@OrderID INT)
RETURNS TABLE
AS
RETURN
(
    SELECT TOP (3)
        ol.OrderID,
        ol.ProductID,
        ol.LineTotal
    FROM Sales.OrderLines AS ol
    WHERE ol.OrderID = @OrderID
    ORDER BY ol.LineTotal DESC
);
GO

SELECT
    o.OrderID,
    top_lines.ProductID,
    top_lines.LineTotal
FROM Sales.Orders AS o
CROSS APPLY Sales.ufnTopOrderLines(o.OrderID) AS top_lines;
GO
SELECT
    o.OrderID,
    top_lines.ProductID,
    top_lines.LineTotal
FROM Sales.Orders AS o
OUTER APPLY Sales.ufnTopOrderLines(o.OrderID) AS top_lines;
GO

Set Operators

SELECT ProductID
FROM Inventory.ActiveProducts
UNION ALL
SELECT ProductID
FROM Inventory.FeaturedProducts;
SELECT ProductID
FROM Inventory.ActiveProducts
INTERSECT
SELECT ProductID
FROM Inventory.FeaturedProducts;
SELECT ProductID
FROM Inventory.ActiveProducts
EXCEPT
SELECT ProductID
FROM Inventory.DiscontinuedProducts;

Module 2: Window Functions & Ranking

OVER (PARTITION BY ... ORDER BY ...) defines how SQL Server slices and orders the analytical window. It lets you calculate running, ranking, or neighbor-aware values without collapsing the underlying detail rows.

Ranking Functions

SELECT
    CustomerID,
    OrderID,
    TotalAmount,
    ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY TotalAmount DESC) AS RowNumberRank,
    RANK() OVER (PARTITION BY CustomerID ORDER BY TotalAmount DESC) AS RankValue,
    DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY TotalAmount DESC) AS DenseRankValue,
    NTILE(4) OVER (ORDER BY TotalAmount DESC) AS RevenueQuartile
FROM Sales.Orders;

NTILE() buckets rows into groups. That is useful for quartiles, deciles, or service-tier segmentation.

Running Totals

SELECT
    OrderDate,
    TotalAmount,
    SUM(TotalAmount) OVER (
        ORDER BY OrderDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RunningRevenue
FROM Sales.Orders
WHERE OrderStatus IN (N'Paid', N'Shipped', N'Delivered')
ORDER BY OrderDate;

Module 3: Common Table Expressions (CTEs)

Standard CTE

In SQL Server, CTEs are not materialized by default. They are mainly a readability feature, not a caching layer. Use them to structure logic cleanly, not as a performance promise.

WITH HighValueOrders AS (
    SELECT
        CustomerID,
        OrderID,
        TotalAmount,
        OrderDate
    FROM Sales.Orders
    WHERE TotalAmount >= 1000.00
)
SELECT
    CustomerID,
    COUNT(*) AS HighValueOrderCount,
    SUM(TotalAmount) AS HighValueRevenue
FROM HighValueOrders
GROUP BY CustomerID
ORDER BY HighValueRevenue DESC;

Recursive CTE

Recursive CTEs walk hierarchies using an anchor member plus a recursive member joined with UNION ALL.

WITH EmployeeHierarchy AS (
    SELECT
        EmployeeID,
        ManagerID,
        EmployeeName,
        0 AS HierarchyDepth,
        CAST(EmployeeName AS NVARCHAR(MAX)) AS ReportingPath
    FROM HR.Employees
    WHERE ManagerID IS NULL

    UNION ALL

    SELECT
        e.EmployeeID,
        e.ManagerID,
        e.EmployeeName,
        eh.HierarchyDepth + 1 AS HierarchyDepth,
        CAST(eh.ReportingPath + N' -> ' + e.EmployeeName AS NVARCHAR(MAX)) AS ReportingPath
    FROM HR.Employees AS e
    INNER JOIN EmployeeHierarchy AS eh
        ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeID, ManagerID, EmployeeName, HierarchyDepth, ReportingPath
FROM EmployeeHierarchy
ORDER BY ReportingPath;
OPTION (MAXRECURSION 100);

Module 4: Advanced Grouping & Pivoting

ROLLUP and CUBE

SELECT
    RegionName,
    ProductCategory,
    SUM(LineTotal) AS Revenue
FROM Sales.FactSales
GROUP BY ROLLUP (RegionName, ProductCategory)
ORDER BY RegionName, ProductCategory;
SELECT
    RegionName,
    SalesChannel,
    SUM(LineTotal) AS Revenue
FROM Sales.FactSales
GROUP BY CUBE (RegionName, SalesChannel)
ORDER BY RegionName, SalesChannel;

The Native PIVOT Operator

PIVOT rotates row values into columns. This is useful for cross-tab style reporting such as monthly revenue by month name.

SELECT
    SalesYear,
    ISNULL([Jan], 0.00) AS Jan,
    ISNULL([Feb], 0.00) AS Feb,
    ISNULL([Mar], 0.00) AS Mar,
    ISNULL([Apr], 0.00) AS Apr
FROM
(
    SELECT
        YEAR(OrderDate) AS SalesYear,
        LEFT(DATENAME(MONTH, OrderDate), 3) AS SalesMonth,
        TotalAmount
    FROM Sales.Orders
    WHERE YEAR(OrderDate) = 2026
) AS source_data
PIVOT
(
    SUM(TotalAmount)
    FOR SalesMonth IN ([Jan], [Feb], [Mar], [Apr])
) AS pivot_result
ORDER BY SalesYear;

UNPIVOT

UNPIVOT turns columns back into rows. It is useful when denormalized spreadsheets or staging tables need to be normalized before processing.

SELECT SalesYear, SalesMonth, Revenue
FROM
(
    SELECT 2026 AS SalesYear, 12000.00 AS Jan, 14100.00 AS Feb, 13850.00 AS Mar, 15040.00 AS Apr
) AS monthly_summary
UNPIVOT
(
    Revenue FOR SalesMonth IN (Jan, Feb, Mar, Apr)
) AS unpivot_result;

Module 5: Stored Procedures & Temporary Structures

Stored procedures are the workhorses of operational T-SQL. They package write-heavy or multi-step logic with parameters, transactions, and predictable behavior.

CREATE OR ALTER PROCEDURE Sales.usp_ProcessOrderBatch
    @BatchDate DATE,
    @MinimumOrderTotal DECIMAL(12, 2) = 100.00,
    @CreatedBy NVARCHAR(100) = N'system'
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #EligibleOrders (
        OrderID INT NOT NULL PRIMARY KEY,
        CustomerID INT NOT NULL,
        TotalAmount DECIMAL(12, 2) NOT NULL
    );

    INSERT INTO #EligibleOrders (OrderID, CustomerID, TotalAmount)
    SELECT
        o.OrderID,
        o.CustomerID,
        o.TotalAmount
    FROM Sales.Orders AS o
    WHERE CAST(o.OrderDate AS DATE) = @BatchDate
      AND o.TotalAmount >= @MinimumOrderTotal;

    CREATE INDEX IX_EligibleOrders_CustomerID
        ON #EligibleOrders (CustomerID);

    UPDATE o
    SET o.OrderStatus = N'ReadyForFulfillment',
        o.ModifiedBy = @CreatedBy,
        o.ModifiedAt = SYSUTCDATETIME()
    FROM Sales.Orders AS o
    INNER JOIN #EligibleOrders AS e
        ON e.OrderID = o.OrderID;
END;
GO
💡
Use #TempTables when the intermediate result is large, reused multiple times, or benefits from indexes. Use @TableVar for small row counts where simplicity matters more than optimizer richness.

Module 6: User-Defined Functions (UDFs)

Scalar UDF

Scalar UDFs are easy to write but can hurt performance. Historically they often executed row by row and blocked optimization. Use them sparingly, especially inside large set-based queries.

CREATE OR ALTER FUNCTION Sales.ufnFormatOrderCode (@OrderID INT)
RETURNS NVARCHAR(30)
AS
BEGIN
    RETURN N'ORD-' + RIGHT(N'000000' + CAST(@OrderID AS NVARCHAR(12)), 6);
END;
GO

Inline Table-Valued Function (iTVF)

iTVFs are the preferred high-performance function pattern in SQL Server. Because they are essentially parameterized views, the optimizer can reason about them much better than many scalar functions or multi-statement TVFs.

CREATE OR ALTER FUNCTION Sales.ufnOrdersByCustomer (@CustomerID INT)
RETURNS TABLE
AS
RETURN
(
    SELECT
        o.OrderID,
        o.OrderDate,
        o.OrderStatus,
        o.TotalAmount
    FROM Sales.Orders AS o
    WHERE o.CustomerID = @CustomerID
);
GO

SELECT *
FROM Sales.ufnOrdersByCustomer(42);
GO

Module 7: Bonus - Error Handling & Dynamic SQL

TRY...CATCH in Stored Procedures

BEGIN TRY
    BEGIN TRANSACTION;

    EXEC Sales.usp_ProcessOrderBatch
        @BatchDate = '2026-04-01',
        @MinimumOrderTotal = 250.00,
        @CreatedBy = N'batch-runner';

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

    THROW;
END CATCH;

Safe Dynamic SQL with sp_executesql

Dynamic SQL is sometimes necessary for flexible filtering, dynamic pivots, or metadata-driven admin scripts. The safe pattern is parameterized execution with sp_executesql, not string-concatenating user input directly into the query.

DECLARE @Sql NVARCHAR(MAX);
DECLARE @Status NVARCHAR(50);

SET @Sql = N'
    SELECT OrderID, CustomerID, OrderStatus, TotalAmount
    FROM Sales.Orders
    WHERE OrderStatus = @OrderStatus;';

SET @Status = N'Paid';

EXEC sp_executesql
    @Sql,
    N'@OrderStatus NVARCHAR(50)',
    @OrderStatus = @Status;
⚠
Security rule: never build a predicate like ... WHERE OrderStatus = ''' + @UserInput + '''. Parameterization is what prevents SQL injection here.

Reference Links