Advanced T-SQL deep dive

-- Applies to: all four platforms
-- A CTE names a step; it is not materialized, and it lives for one statement.
WITH OrderTotals AS (
    SELECT CustomerID, SUM(Total) AS Spend
    FROM dbo.OrderHeader
    WHERE OrderDate >= '2026-01-01'
    GROUP BY CustomerID
)
SELECT c.Name, t.Spend
FROM OrderTotals AS t
JOIN dbo.Customer AS c ON c.CustomerID = t.CustomerID
WHERE t.Spend > 10000;

-- Recursive CTE: anchor member UNION ALL recursive member
WITH OrgChart AS (
    SELECT EmployeeID, ManagerID, Name, 0 AS Level
    FROM dbo.Employee
    WHERE ManagerID IS NULL                               -- anchor: the root
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.Name, oc.Level + 1
    FROM dbo.Employee AS e
    JOIN OrgChart AS oc ON e.ManagerID = oc.EmployeeID     -- recursive member
)
SELECT * FROM OrgChart
OPTION (MAXRECURSION 200);                                 -- default 100; 0 = unlimited

-- Applies to: all four platforms (WINDOW clause and IGNORE NULLS: SQL Server 2022+ and Azure SQL)
SELECT  CustomerID, OrderNo, OrderDate, Total,
        ROW_NUMBER() OVER w                AS rn,          -- 1, 2, 3, 4 — unique
        RANK()       OVER w                AS rnk,         -- 1, 2, 2, 4 — gaps after ties
        DENSE_RANK() OVER w                AS drnk,        -- 1, 2, 2, 3 — no gaps
        NTILE(4)     OVER (ORDER BY Total) AS quartile,
        LAG(Total)  OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS prev_total,
        LEAD(OrderDate, 1, '9999-12-31') OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS next_order,
        SUM(Total) OVER (PARTITION BY CustomerID ORDER BY OrderDate
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
        AVG(Total) OVER (PARTITION BY CustomerID ORDER BY OrderDate
                         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)         AS moving_avg_3,
        LAST_VALUE(Total) OVER (PARTITION BY CustomerID ORDER BY OrderDate
                         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_total
FROM dbo.OrderHeader
WINDOW w AS (PARTITION BY CustomerID ORDER BY Total DESC);

-- Top 3 orders per customer: window functions can't go in WHERE, so filter in an outer query
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY Total DESC) AS rn
    FROM dbo.OrderHeader
)
SELECT * FROM ranked WHERE rn <= 3;

-- Build JSON
SELECT JSON_OBJECT('id': c.CustomerID, 'name': c.Name, 'vip': CAST(c.IsVip AS bit)
                   ABSENT ON NULL) AS customer_json            -- default for JSON_OBJECT is NULL ON NULL
FROM dbo.Customer AS c;

SELECT JSON_ARRAY(1, N'two', NULL, 4) AS arr;                -- default ABSENT ON NULL → [1,"two",4]

-- Aggregate rows into JSON (SQL Server 2025+, Azure SQL, Fabric)
SELECT c.CustomerID,
       JSON_ARRAYAGG(o.OrderNo ORDER BY o.OrderDate)         AS order_numbers,
       JSON_OBJECTAGG(CAST(o.OrderNo AS nvarchar(20)) : o.Total) AS totals_by_order
FROM dbo.Customer AS c
JOIN dbo.OrderHeader AS o ON o.CustomerID = c.CustomerID
GROUP BY c.CustomerID;

-- Shape a whole result set as JSON
SELECT TOP (3) o.OrderNo AS [order.no], o.OrderDate AS [order.date], c.Name AS [customer.name]
FROM dbo.OrderHeader AS o JOIN dbo.Customer AS c ON c.CustomerID = o.CustomerID
FOR JSON PATH, ROOT('orders'), INCLUDE_NULL_VALUES;           -- dot aliases nest objects

DECLARE @doc json = N'{
  "order": { "no": 1001, "status": "shipped",
             "lines": [ {"sku": "A-1", "qty": 2}, {"sku": "B-7", "qty": 1} ] },
  "tags": ["priority", "gift"] }';

SELECT JSON_VALUE(@doc, '$.order.status')           AS status,       -- scalar → nvarchar(4000)
       JSON_QUERY(@doc, '$.order.lines')            AS lines,        -- object or array
       JSON_PATH_EXISTS(@doc, '$.order.coupon')     AS has_coupon,   -- 0
       JSON_CONTAINS(@doc, 'gift', '$.tags[*]')     AS is_gift,      -- 1 (SQL Server 2025+, Azure SQL, Fabric)
       JSON_VALUE(@doc, 'strict $.order.missing')   AS boom;         -- strict: error instead of NULL

-- Shred an array into rows with a typed schema
SELECT l.sku, l.qty
FROM OPENJSON(@doc, '$.order.lines')
     WITH (sku nvarchar(20) '$.sku',
           qty int          '$.qty') AS l;

-- Update in place
SET @doc = JSON_MODIFY(@doc, '$.order.status', 'delivered');
SET @doc = JSON_MODIFY(@doc, 'append $.tags', 'reviewed');

-- Applies to: SQL Server 2025, Azure SQL Database, Azure SQL MI (2025/AUTD), SQL database in Fabric
-- REGEXP_LIKE and the table-valued functions need compatibility level 170.

-- Validate: a CHECK constraint with REGEXP_LIKE
ALTER TABLE dbo.Customer ADD CONSTRAINT CK_Customer_Email
    CHECK (REGEXP_LIKE(Email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'));

SELECT Name
FROM dbo.Customer
WHERE REGEXP_LIKE(Phone, '^\+34 ?[6-9]\d{8}$');           -- anchored with ^ → SARGable

SELECT REGEXP_REPLACE(N'Call 555-123-4567 now', '\d{3}-\d{3}-(\d{4})', 'XXX-XXX-\1') AS masked,  -- \1 backreference in replacement
       REGEXP_SUBSTR(N'ORD-2026-00042', '\d+', 1, 2)         AS second_number,  -- 00042
       REGEXP_INSTR(N'abc123', '\d')                         AS first_digit_at, -- 4
       REGEXP_COUNT(N'a,b,,c', ',')                           AS commas;         -- 3

-- Table-valued: every match, and split by a pattern
SELECT match_id, match_value FROM REGEXP_MATCHES(N'#sql #ai #vectors', '#(\w+)');
SELECT value FROM REGEXP_SPLIT_TO_TABLE(N'red; green ,blue', '\s*[;,]\s*');

-- Preview on all four platforms; SQL Server 2025 needs PREVIEW_FEATURES = ON.
-- Supported with Windows and binary collations, not non-binary SQL_* collations.
SELECT EDIT_DISTANCE(N'Jonathan', N'Johnathan')               AS edits,          -- 1
       EDIT_DISTANCE_SIMILARITY(N'Jonathan', N'Johnathan')    AS edit_sim,       -- 0–100
       JARO_WINKLER_DISTANCE(N'Martha', N'Marhta')            AS jw_distance,    -- float, 0 = identical
       JARO_WINKLER_SIMILARITY(N'Martha', N'Marhta')          AS jw_sim;         -- 0–100, rewards a shared prefix

-- Find probable duplicate customers
SELECT a.CustomerID, b.CustomerID, a.Name, b.Name
FROM dbo.Customer AS a
JOIN dbo.Customer AS b ON a.CustomerID < b.CustomerID
WHERE a.PostalCode = b.PostalCode                              -- block first: fuzzy compares are per pair
  AND JARO_WINKLER_SIMILARITY(a.Name, b.Name) >= 90;

-- Applies to: SQL Server 2017+, Azure SQL Database, Azure SQL MI
-- Friends of Alice, and friends of her friends (two hops)
SELECT p2.Name AS friend, p3.Name AS friend_of_friend
FROM dbo.Person AS p1, dbo.FriendOf AS f1, dbo.Person AS p2,
     dbo.FriendOf AS f2, dbo.Person AS p3
WHERE MATCH(p1-(f1)->p2-(f2)->p3)                              -- arrow = edge direction
  AND p1.Name = N'Alice';

-- Shortest path from Alice to everyone reachable, with the path as text
SELECT p1.Name AS start_person,
       STRING_AGG(p2.Name, N' -> ') WITHIN GROUP (GRAPH PATH) AS path,
       LAST_VALUE(p2.Name)          WITHIN GROUP (GRAPH PATH) AS reached,
       COUNT(p2.PersonID)           WITHIN GROUP (GRAPH PATH) AS hops
FROM dbo.Person AS p1,
     dbo.FriendOf FOR PATH AS fo,
     dbo.Person   FOR PATH AS p2
WHERE MATCH(SHORTEST_PATH(p1(-(fo)->p2)+))                     -- + = one or more hops; {1,3} = up to three
  AND p1.Name = N'Alice';

-- EXISTS: a semi-join, stops at the first match
SELECT c.CustomerID, c.Name
FROM dbo.Customer AS c
WHERE EXISTS (SELECT 1 FROM dbo.OrderHeader AS o
              WHERE o.CustomerID = c.CustomerID AND o.OrderDate >= '2026-10-01');

-- NOT IN with a NULL in the subquery returns no rows at all — use NOT EXISTS
SELECT p.ProductID
FROM dbo.Product AS p
WHERE NOT EXISTS (SELECT 1 FROM dbo.OrderLine AS l WHERE l.ProductID = p.ProductID);

-- Correlated scalar subquery: one value per outer row
SELECT c.CustomerID,
       (SELECT MAX(o.OrderDate) FROM dbo.OrderHeader AS o WHERE o.CustomerID = c.CustomerID) AS last_order
FROM dbo.Customer AS c;

-- CROSS APPLY: a correlated table expression — the last two orders per customer
SELECT c.Name, x.OrderNo, x.Total
FROM dbo.Customer AS c
CROSS APPLY (SELECT TOP (2) o.OrderNo, o.Total
             FROM dbo.OrderHeader AS o
             WHERE o.CustomerID = c.CustomerID
             ORDER BY o.OrderDate DESC) AS x;      -- OUTER APPLY keeps customers with no orders

CREATE OR ALTER PROCEDURE dbo.usp_TransferStock
    @FromWarehouse int, @ToWarehouse int, @ProductID int, @Qty int
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;                         -- runtime errors doom the transaction
    BEGIN TRY
        IF @Qty <= 0
            THROW 50010, N'Quantity must be positive.', 1;   -- user error: number >= 50000, severity 16

        BEGIN TRANSACTION;
            UPDATE dbo.Stock SET Qty -= @Qty WHERE WarehouseID = @FromWarehouse AND ProductID = @ProductID;
            IF @@ROWCOUNT = 0 THROW 50011, N'No stock row to move from.', 1;
            UPDATE dbo.Stock SET Qty += @Qty WHERE WarehouseID = @ToWarehouse AND ProductID = @ProductID;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;   -- -1 uncommittable, 1 committable, 0 none
        INSERT dbo.ErrorLog (Number, Severity, State, ProcName, Line, Message)
        VALUES (ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(),
                ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE());
        THROW;                                       -- re-raise the original error unchanged
    END CATCH;
END;

-- MERGE: one statement for insert, update and delete against a source
MERGE dbo.Product WITH (HOLDLOCK) AS t          -- HOLDLOCK avoids a race between the match and the insert
USING staging.Product AS s
    ON t.Sku = s.Sku
WHEN MATCHED AND (t.Name <> s.Name OR t.ListPrice <> s.ListPrice) THEN
    UPDATE SET t.Name = s.Name, t.ListPrice = s.ListPrice
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Sku, Name, ListPrice, CategoryID) VALUES (s.Sku, s.Name, s.ListPrice, s.CategoryID)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action, inserted.Sku, deleted.ListPrice AS old_price;   -- the statement must end with ;