SQL Server 2017 Complete Playground

Enable Query Store + set compatibility level

ALTER DATABASE TechoralDB SET COMPATIBILITY_LEVEL = 140;  -- SQL Server 2017
ALTER DATABASE TechoralDB SET RECOVERY SIMPLE;

ALTER DATABASE TechoralDB SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    MAX_STORAGE_SIZE_MB = 100
);

-- Business schemas
CREATE SCHEMA Sales;    CREATE SCHEMA HR;
CREATE SCHEMA Inventory; CREATE SCHEMA Audit;
CREATE SCHEMA Reports;

SEQUENCE — reusable auto-number (alternative to IDENTITY)

CREATE SEQUENCE Sales.OrderNumberSeq
    START WITH 100001 INCREMENT BY 1
    MINVALUE 100001   MAXVALUE 9999999
    CYCLE;

-- Use it as a default:
CREATE TABLE Sales.Orders
(
    OrderID    INT  NOT NULL DEFAULT (NEXT VALUE FOR Sales.OrderNumberSeq),
    RowVersion ROWVERSION,   -- for optimistic concurrency
    ...
);

OUTPUT — capture inserted IDs into a table variable

DECLARE @InsertedOrders TABLE (OrderID INT, CustomerID INT);

INSERT INTO Sales.Orders (CustomerID, EmpID, Status)
OUTPUT INSERTED.OrderID, INSERTED.CustomerID INTO @InsertedOrders
VALUES (1, 1003, 'Pending');

SELECT * FROM @InsertedOrders;

Recursive CTE — org-chart traversal

WITH OrgChart AS
(
    -- Anchor: top-level (no manager)
    SELECT EmpID, FullName, JobTitle, ManagerID, 0 AS Level,
           CAST(FullName AS NVARCHAR(1000)) AS Path
    FROM HR.Employees WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive: reports to someone already in result
    SELECT e.EmpID, e.FullName, e.JobTitle, e.ManagerID,
           oc.Level + 1,
           CAST(oc.Path + ' > ' + e.FullName AS NVARCHAR(1000))
    FROM HR.Employees e
        INNER JOIN OrgChart oc ON e.ManagerID = oc.EmpID
)
SELECT REPLICATE('  ', Level) + FullName AS OrgTree,
       JobTitle, Level, Path
FROM OrgChart
OPTION (MAXRECURSION 10);

LEAD / LAG — month-over-month revenue change

WITH Monthly AS (
    SELECT YEAR(OrderDate) yr, MONTH(OrderDate) mo,
           SUM(TotalAmount) Revenue
    FROM Sales.Orders WHERE Status <> 'Cancelled'
    GROUP BY YEAR(OrderDate), MONTH(OrderDate)
)
SELECT yr, mo, Revenue,
    LAG(Revenue)  OVER (ORDER BY yr, mo) AS PrevMonth,
    LEAD(Revenue) OVER (ORDER BY yr, mo) AS NextMonth,
    Revenue - LAG(Revenue) OVER (ORDER BY yr, mo) AS MoMChange,
    FORMAT(100.0 * (Revenue - LAG(Revenue) OVER (ORDER BY yr, mo))
           / NULLIF(LAG(Revenue) OVER (ORDER BY yr, mo), 0), 'N2') + '%' AS MoMPct
FROM Monthly ORDER BY yr, mo;

Safe dynamic SQL with sp_executesql (parameterised — no injection)

CREATE OR ALTER PROCEDURE Reports.usp_SearchEmployees
    @DeptID INT = NULL, @JobTitle NVARCHAR(100) = NULL,
    @MinSalary DECIMAL(12,2) = NULL
AS
BEGIN
    DECLARE @sql   NVARCHAR(MAX) = N'
        SELECT e.FullName, e.JobTitle, e.Salary, d.DeptName
        FROM HR.Employees e INNER JOIN HR.Departments d ON e.DeptID = d.DeptID
        WHERE e.IsActive = 1';

    IF @DeptID    IS NOT NULL SET @sql += N' AND e.DeptID = @pDeptID';
    IF @JobTitle  IS NOT NULL SET @sql += N' AND e.JobTitle LIKE @pJobTitle';
    IF @MinSalary IS NOT NULL SET @sql += N' AND e.Salary >= @pMinSalary';

    EXEC sp_executesql @sql,
        N'@pDeptID INT, @pJobTitle NVARCHAR(100), @pMinSalary DECIMAL(12,2)',
        @pDeptID = @DeptID, @pJobTitle = @JobTitle, @pMinSalary = @MinSalary;
END;

Indexed (materialised) view — pre-aggregate order stats

CREATE OR ALTER VIEW Sales.vw_ProductSalesSummary
WITH SCHEMABINDING AS
SELECT oi.ProductID,
       COUNT_BIG(*)      AS OrderLineCount,
       SUM(oi.Quantity)  AS TotalQtySold,
       SUM(oi.LineTotal) AS TotalRevenue
FROM Sales.OrderItems oi
GROUP BY oi.ProductID;

-- The unique clustered index materialises it on disk
CREATE UNIQUE CLUSTERED INDEX IX_vw_ProductSalesSummary
    ON Sales.vw_ProductSalesSummary (ProductID);

SQL Server automatically uses this view when the optimizer detects a matching aggregate query on OrderItems — even if your query doesn't reference the view directly.

Isolation level quick reference

-- READ UNCOMMITTED: dirty reads, fastest
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- READ COMMITTED (default): only committed rows
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- REPEATABLE READ: rows you've read can't be changed by others
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- SERIALIZABLE: strictest — no phantom rows
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- SNAPSHOT: readers don't block writers (row versioning)
ALTER DATABASE TechoralDB SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE TechoralDB SET READ_COMMITTED_SNAPSHOT ON;
-- Once RCSI is ON, no code changes needed — READ COMMITTED
-- automatically uses row versions. Zero blocking.

Columnstore index — 10–100× faster analytics

-- Non-clustered columnstore on the fact table
-- (coexists with row-store — no sacrifice for OLTP)
CREATE NONCLUSTERED COLUMNSTORE INDEX NCI_FactOrders_Analytics
    ON Sales.FactOrders (OrderDate, Category, Country, LineTotal, Quantity);

-- Analytical query now uses batch-mode execution
SELECT Category, Country, YEAR(OrderDate),
       SUM(Quantity), SUM(LineTotal)
FROM Sales.FactOrders
GROUP BY Category, Country, YEAR(OrderDate)
ORDER BY SUM(LineTotal) DESC;

Temporal table — define once, get free history

CREATE TABLE HR.EmployeeSalaryHistory
(
    EmpID     INT             NOT NULL,
    Salary    DECIMAL(12,2)   NOT NULL,
    JobTitle  NVARCHAR(100)   NOT NULL,
    ValidFrom DATETIME2(7) GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo   DATETIME2(7) GENERATED ALWAYS AS ROW END   NOT NULL,
    CONSTRAINT PK_EmpSalaryHistory PRIMARY KEY (EmpID),
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (
    HISTORY_TABLE = HR.EmployeeSalaryHistory_History
));

Row-Level Security — sales reps see only their own orders

-- Predicate function (returns 1 = allow access)
CREATE FUNCTION Security2.fn_OrderFilter (@EmpID INT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN (
    SELECT 1 AS AccessGranted
    WHERE @EmpID = TRY_CAST(SESSION_CONTEXT(N'EmpID') AS INT)
       OR IS_MEMBER('db_owner') = 1
);

-- Attach to the table as a security policy
CREATE SECURITY POLICY Sales.OrderRLSPolicy
    ADD FILTER PREDICATE Security2.fn_OrderFilter(EmpID) ON Sales.Orders,
    ADD BLOCK  PREDICATE Security2.fn_OrderFilter(EmpID) ON Sales.Orders AFTER INSERT
WITH (STATE = ON);

-- Set session context (e.g. from your application login)
EXEC sp_set_session_context N'EmpID', 1004;
SELECT * FROM Sales.Orders;  -- automatically sees only EmpID=1004's orders

Point-in-time restore (3-step process)

-- 1. Tail-log backup (captures last log before restore)
BACKUP LOG TechoralDB
    TO DISK = 'C:\Temp\TechoralDB_TailLog.bak'
    WITH NORECOVERY, COMPRESSION, CHECKSUM;

-- 2. Restore full backup — leave in RESTORING state
RESTORE DATABASE TechoralDB_Recovered
    FROM DISK = 'C:\Temp\TechoralDB_Full.bak'
    WITH MOVE '...' TO '...restored.mdf',
         NORECOVERY, STATS = 10;

-- 3. Restore log — stop BEFORE the disaster timestamp
RESTORE LOG TechoralDB_Recovered
    FROM DISK = 'C:\Temp\TechoralDB_TailLog.bak'
    WITH RECOVERY,
         STOPAT = '2026-06-03T09:59:59';  -- 1 second before the accident

Enable CDC and query changes

EXEC sys.sp_cdc_enable_db;

EXEC sys.sp_cdc_enable_table
    @source_schema = N'Sales', @source_name = N'Orders',
    @role_name = NULL, @supports_net_changes = 1;

-- Query all changes since last LSN
DECLARE @from_lsn BINARY(10) = sys.fn_cdc_get_min_lsn('Sales_Orders');
DECLARE @to_lsn   BINARY(10) = sys.fn_cdc_get_max_lsn();

SELECT
    CASE __$operation
        WHEN 1 THEN 'Delete'   WHEN 2 THEN 'Insert'
        WHEN 3 THEN 'Before U' WHEN 4 THEN 'After U' END AS Op,
    OrderID, Status, TotalAmount
FROM cdc.fn_cdc_get_all_changes_Sales_Orders(@from_lsn, @to_lsn, 'all');