SQL Server JSON, Temporal Tables, String Functions and Sequences (2026)

1. JSON Support in SQL Server 2016+

SQL Server 2016 introduced first-class JSON support without adding a dedicated JSON column type. JSON is stored as NVARCHAR(MAX) — keeping compatibility with existing tooling — while a rich set of built-in functions lets you read, write, validate, and stream JSON within pure T-SQL.

Why Store JSON in SQL Server?

The pattern arises frequently in modern applications:

• API payloads — log the raw request/response body alongside the parsed relational columns.
• Flexible schemas — store per-customer configuration or metadata without adding dozens of nullable columns.
• Event sourcing — persist a JSON change payload in an audit table next to a temporal history record.
• Integration staging — import a JSON array from an external API into rows using OPENJSON() before ETL processing.

Sample Table

All examples in sections 2–7 use this table:

CREATE TABLE dbo.Orders (
    OrderID     INT PRIMARY KEY,
    CustomerID  INT NOT NULL,
    OrderDate   DATE NOT NULL,
    Details     NVARCHAR(MAX) -- stores JSON
      CONSTRAINT chk_Orders_Details_JSON CHECK (ISJSON(Details) = 1)
);

INSERT INTO dbo.Orders VALUES
(1, 101, '2026-01-10',
 N'{"product":"Widget","qty":3,"price":9.99,"tags":["sale","bulk"]}'),
(2, 102, '2026-02-14',
 N'{"product":"Gadget","qty":1,"price":49.00,"tags":["new"]}'),
(3, 101, '2026-03-22',
 N'{"product":"Widget","qty":10,"price":8.99,"tags":["bulk","vip"]}');

2. JSON_VALUE(): Extracting Scalar Values

JSON_VALUE(expression, path) extracts a single scalar value (string, number, boolean) from a JSON document. The result type is NVARCHAR(4000) — cast it explicitly when you need a numeric or date type.

Basic Path Expressions

-- Extract product name and price from each order
SELECT
    OrderID,
    JSON_VALUE(Details, '$.product')        AS Product,
    CAST(JSON_VALUE(Details, '$.price') AS DECIMAL(10,2)) AS UnitPrice,
    JSON_VALUE(Details, '$.qty')            AS Quantity
FROM dbo.Orders;
-- Result:
-- OrderID  Product  UnitPrice  Quantity
-- 1        Widget   9.99       3
-- 2        Gadget   49.00      1
-- 3        Widget   8.99       10

Strict vs Lax Mode

SQL Server path expressions support two modes prepended with a keyword:

• lax (default) — if the path does not exist or the value is an object/array, JSON_VALUE returns NULL silently.
• strict — if the path does not exist or the value is not scalar, SQL Server raises an error.

-- lax mode (default) — returns NULL for missing path
SELECT JSON_VALUE(Details, 'lax $.discount') AS Discount
FROM dbo.Orders;          -- returns NULL for all rows (path absent)

-- strict mode — raises error if path absent
SELECT JSON_VALUE(Details, 'strict $.discount') AS Discount
FROM dbo.Orders;          -- ERROR: Property cannot be found

Indexing a JSON Path

-- Add a computed column, then index it
ALTER TABLE dbo.Orders
  ADD ProductName AS JSON_VALUE(Details, '$.product') PERSISTED;

CREATE INDEX IX_Orders_ProductName ON dbo.Orders (ProductName);

-- Now this query gets an index seek:
SELECT OrderID, OrderDate
FROM dbo.Orders
WHERE JSON_VALUE(Details, '$.product') = 'Widget';

3. JSON_QUERY(): Extracting Objects and Arrays

Where JSON_VALUE extracts a scalar, JSON_QUERY(expression, path) extracts a JSON fragment — an object or array — and returns it as NVARCHAR(MAX). Attempting to use JSON_VALUE on an object/array path in lax mode returns NULL; JSON_QUERY is the correct tool.

-- Extract the full tags array as a JSON string
SELECT
    OrderID,
    JSON_QUERY(Details, '$.tags')   AS TagsArray,
    JSON_VALUE(Details, '$.tags')   AS TagsValue  -- returns NULL (array, not scalar)
FROM dbo.Orders;
-- OrderID  TagsArray           TagsValue
-- 1        ["sale","bulk"]     NULL
-- 2        ["new"]             NULL
-- 3        ["bulk","vip"]      NULL

Nested Objects

-- Suppose Details contains a nested shipping object:
-- {"product":"Widget","qty":3,"price":9.99,
--  "shipping":{"carrier":"FedEx","tracking":"123XYZ"}}

SELECT
    OrderID,
    JSON_QUERY(Details, '$.shipping')               AS ShippingObject,
    JSON_VALUE(Details, '$.shipping.carrier')       AS Carrier,
    JSON_VALUE(Details, '$.shipping.tracking')      AS TrackingNumber
FROM dbo.Orders
WHERE JSON_VALUE(Details, '$.shipping.carrier') IS NOT NULL;

4. JSON_MODIFY(): Updating JSON Values In Place

JSON_MODIFY(expression, path, newValue) returns a modified copy of the JSON string. It does not mutate in place — you assign the result back with UPDATE … SET col = JSON_MODIFY(col, …).

-- Raise the price by 10% for all Widget orders
UPDATE dbo.Orders
SET Details = JSON_MODIFY(
    Details,
    '$.price',
    CAST(JSON_VALUE(Details, '$.price') AS DECIMAL(10,2)) * 1.10
)
WHERE JSON_VALUE(Details, '$.product') = 'Widget';

-- Add a new property (discount) that did not exist before
UPDATE dbo.Orders
SET Details = JSON_MODIFY(Details, '$.discount', 5.00)
WHERE OrderID = 1;

-- Delete a property by setting it to NULL with strict lax path
UPDATE dbo.Orders
SET Details = JSON_MODIFY(Details, '$.discount', NULL)
WHERE OrderID = 1;

5. ISJSON() and JSON_PATH_EXISTS(): Validation and Conditional Queries

ISJSON()

ISJSON(expression) returns 1 if the string is valid JSON, 0 otherwise, and NULL if the input is NULL. Use it in CHECK constraints (shown above) or to filter a table that may hold mixed content:

SELECT OrderID, Details
FROM dbo.Orders
WHERE ISJSON(Details) = 1;   -- skip any rows with malformed JSON

JSON_PATH_EXISTS() (SQL Server 2022)

SQL Server 2022 added JSON_PATH_EXISTS(expression, path) which returns 1 if the path exists in the document, 0 if not, and NULL on NULL input. Before 2022, the equivalent was checking JSON_VALUE(…) IS NOT NULL, which is ambiguous if the value really is null.

-- SQL Server 2022+
SELECT OrderID
FROM dbo.Orders
WHERE JSON_PATH_EXISTS(Details, '$.discount') = 1;  -- only rows that have a discount key

-- Pre-2022 workaround (lax mode returns NULL for missing path OR for null value)
SELECT OrderID
FROM dbo.Orders
WHERE JSON_VALUE(Details, 'strict $.discount') IS NOT NULL;  -- errors if path absent
-- safer pre-2022 pattern:
WHERE TRY_CAST(JSON_VALUE(Details, '$.discount') AS DECIMAL(10,2)) IS NOT NULL;

6. FOR JSON PATH vs FOR JSON AUTO: Serialising Results as JSON

The FOR JSON clause appended to a SELECT statement returns the result set as a JSON array instead of a tabular rowset — the reverse of OPENJSON.

FOR JSON AUTO

FOR JSON AUTO infers the JSON structure from the query's table aliases. Simple but inflexible — nesting depends on JOIN order.

SELECT o.OrderID, o.OrderDate,
       c.CustomerID, c.CompanyName
FROM dbo.Orders o
JOIN dbo.Customers c ON c.CustomerID = o.CustomerID
FOR JSON AUTO;
-- Returns:
-- [{"OrderID":1,"OrderDate":"2026-01-10",
--   "c":[{"CustomerID":101,"CompanyName":"Acme Corp"}]},...]

FOR JSON PATH

FOR JSON PATH gives full control via column aliases. Use dot notation in the alias to create nested objects:

SELECT
    o.OrderID                                          AS "order.id",
    o.OrderDate                                        AS "order.date",
    JSON_VALUE(o.Details, '$.product')                AS "order.product",
    CAST(JSON_VALUE(o.Details, '$.price') AS DECIMAL) AS "order.price",
    o.CustomerID                                       AS "customer.id"
FROM dbo.Orders o
FOR JSON PATH, ROOT('orders');
-- Returns:
-- {"orders":[
--   {"order":{"id":1,"date":"2026-01-10","product":"Widget","price":10},
--    "customer":{"id":101}},
--   ...
-- ]}

7. OPENJSON(): Parsing JSON Arrays into Rows

OPENJSON(json_expression [, path]) is a table-valued function that shreds a JSON array or object into a relational rowset. It is the standard way to bulk-import JSON data into SQL Server tables.

Default Schema (Key/Value Pairs)

-- Parse a single document's top-level key-value pairs
SELECT [key], [value], [type]
FROM OPENJSON(
  N'{"product":"Widget","qty":3,"price":9.99}'
);
-- key      value    type
-- product  Widget   1 (string)
-- qty      3        2 (number)
-- price    9.99     2 (number)

WITH Clause (Explicit Schema)

The WITH clause maps JSON paths to typed columns — far more useful for real import scenarios:

-- Import a JSON array of new orders
DECLARE @json NVARCHAR(MAX) = N'[
  {"id":10,"product":"Sprocket","qty":5,"price":2.50},
  {"id":11,"product":"Cog","qty":2,"price":7.00},
  {"id":12,"product":"Gear","qty":8,"price":3.25}
]';

INSERT INTO dbo.Orders (OrderID, CustomerID, OrderDate, Details)
SELECT
    j.OrderID,
    999,                -- placeholder customer
    CAST(GETDATE() AS DATE),
    (SELECT j.Product, j.Qty, j.Price FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
FROM OPENJSON(@json)
WITH (
    OrderID  INT           '$.id',
    Product  NVARCHAR(100) '$.product',
    Qty      INT           '$.qty',
    Price    DECIMAL(10,2) '$.price'
) AS j;

Expanding a JSON Array Column

-- Expand the tags array stored in each order's Details column
SELECT o.OrderID, tag.[value] AS Tag
FROM dbo.Orders o
CROSS APPLY OPENJSON(o.Details, '$.tags') AS tag;
-- OrderID  Tag
-- 1        sale
-- 1        bulk
-- 2        new
-- 3        bulk
-- 3        vip

8. Temporal Tables (System-Versioned): CREATE TABLE with PERIOD FOR SYSTEM_TIME

System-versioned temporal tables, introduced in SQL Server 2016 (ISO/IEC SQL:2011), automatically maintain a full history of every row change. SQL Server handles history writes transparently — no triggers, no manual audit tables.

Creating a Temporal Table

Three additions to a normal CREATE TABLE statement are required:

1. Two DATETIME2 columns to hold the validity period (ValidFrom, ValidTo).
2. A PERIOD FOR SYSTEM_TIME declaration linking those columns.
3. WITH (SYSTEM_VERSIONING = ON), optionally naming the history table.

CREATE TABLE dbo.ProductPrices (
    ProductID   INT           NOT NULL PRIMARY KEY,
    ProductName NVARCHAR(200) NOT NULL,
    ListPrice   DECIMAL(10,2) NOT NULL,
    Category    NVARCHAR(100) NOT NULL,
    -- System-period columns (auto-managed by SQL Server)
    ValidFrom   DATETIME2     GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo     DATETIME2     GENERATED ALWAYS AS ROW END   NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.ProductPricesHistory,
        DATA_CONSISTENCY_CHECK = ON
    )
);

-- Check both tables exist
SELECT name, temporal_type_desc
FROM sys.tables
WHERE name LIKE 'ProductPrices%';

DML on a Temporal Table

-- INSERT: SQL Server sets ValidFrom = current UTC time, ValidTo = max datetime2
INSERT INTO dbo.ProductPrices VALUES (1, 'Widget Pro', 29.99, 'Hardware');
INSERT INTO dbo.ProductPrices VALUES (2, 'Gadget Max', 99.00, 'Electronics');

-- UPDATE: SQL Server copies the OLD row to history (with ValidTo = now),
-- then updates the current table (ValidFrom = now)
UPDATE dbo.ProductPrices SET ListPrice = 24.99 WHERE ProductID = 1;

-- DELETE: SQL Server moves the row to history with ValidTo = now.
-- The current table row is gone, but history retains it.
DELETE FROM dbo.ProductPrices WHERE ProductID = 2;

9. Querying Temporal History: FOR SYSTEM_TIME Clauses

SQL Server extends the FROM clause with FOR SYSTEM_TIME sub-clauses that let you query the current table, the history table, or a union of both — without writing any JOIN to the history table yourself.

-- 1. AS OF — state of the table at a specific point in time
SELECT ProductID, ProductName, ListPrice
FROM dbo.ProductPrices
FOR SYSTEM_TIME AS OF '2026-03-01T00:00:00';

-- 2. FROM ... TO — all row versions that were active
--    at any point in the half-open interval [start, end)
SELECT ProductID, ProductName, ListPrice, ValidFrom, ValidTo
FROM dbo.ProductPrices
FOR SYSTEM_TIME FROM '2026-01-01' TO '2026-04-01';

-- 3. BETWEEN ... AND — closed interval (includes both endpoints)
SELECT ProductID, ListPrice, ValidFrom, ValidTo
FROM dbo.ProductPrices
FOR SYSTEM_TIME BETWEEN '2026-01-01' AND '2026-06-01';

-- 4. CONTAINED IN — only rows FULLY contained within the interval
SELECT ProductID, ListPrice, ValidFrom, ValidTo
FROM dbo.ProductPrices
FOR SYSTEM_TIME CONTAINED IN ('2026-02-01', '2026-05-01');

-- 5. ALL — union of current table and entire history
SELECT ProductID, ListPrice, ValidFrom, ValidTo
FROM dbo.ProductPrices
FOR SYSTEM_TIME ALL
ORDER BY ProductID, ValidFrom;

Price Change Audit Report

-- Show every price change for a product with the delta
SELECT
    ProductID,
    ProductName,
    ListPrice                                               AS NewPrice,
    LAG(ListPrice) OVER (PARTITION BY ProductID ORDER BY ValidFrom)
                                                           AS OldPrice,
    ListPrice - LAG(ListPrice) OVER (PARTITION BY ProductID ORDER BY ValidFrom)
                                                           AS PriceDelta,
    ValidFrom                                              AS ChangedAt
FROM dbo.ProductPrices
FOR SYSTEM_TIME ALL
WHERE ProductID = 1
ORDER BY ValidFrom;

10. Temporal Table Use Cases: Price History, Audit Log, Point-in-Time Restore

Price History and SLA Compliance

-- What was the price when each order was placed?
SELECT
    o.OrderID,
    o.OrderDate,
    p.ProductName,
    p.ListPrice AS PriceAtOrderTime
FROM dbo.Orders o
JOIN dbo.ProductPrices
     FOR SYSTEM_TIME AS OF o.OrderDate AS p
     ON p.ProductID = CAST(JSON_VALUE(o.Details, '$.productId') AS INT);

Point-in-Time Restore

-- Accidentally deleted rows? Recover from temporal history:
INSERT INTO dbo.ProductPrices (ProductID, ProductName, ListPrice, Category)
SELECT ProductID, ProductName, ListPrice, Category
FROM dbo.ProductPrices
FOR SYSTEM_TIME AS OF '2026-05-31T23:59:00'  -- just before the bad DELETE
WHERE ProductID IN (2, 5, 9);                 -- rows that were deleted

Disabling System Versioning (Schema Changes)

-- You must disable versioning before ALTER TABLE
ALTER TABLE dbo.ProductPrices SET (SYSTEM_VERSIONING = OFF);

ALTER TABLE dbo.ProductPrices ADD Discount DECIMAL(5,2) NULL;

ALTER TABLE dbo.ProductPricesHistory ADD Discount DECIMAL(5,2) NULL;

ALTER TABLE dbo.ProductPrices
  SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductPricesHistory));

11. STRING_AGG(): Concatenating Rows into a String

STRING_AGG(expression, separator), available since SQL Server 2017, concatenates values from multiple rows into a single delimited string. It replaces the verbose and fragile FOR XML PATH('') hack that had been the standard for over a decade.

Basic Syntax

-- List all tags per order as a comma-separated string
-- First expand with OPENJSON, then re-aggregate with STRING_AGG
SELECT
    o.OrderID,
    STRING_AGG(t.[value], ', ') AS Tags
FROM dbo.Orders o
CROSS APPLY OPENJSON(o.Details, '$.tags') AS t
GROUP BY o.OrderID;
-- OrderID  Tags
-- 1        sale, bulk
-- 2        new
-- 3        bulk, vip

WITHIN GROUP (ORDER BY)

Use the optional WITHIN GROUP clause to sort the concatenated values:

-- Employees per department, names alphabetically sorted
SELECT
    DepartmentID,
    STRING_AGG(LastName, '; ') WITHIN GROUP (ORDER BY LastName ASC) AS EmployeeList
FROM HR.Employees
GROUP BY DepartmentID;

Replacing the FOR XML PATH Hack

-- OLD pattern (SQL Server 2008–2016 era) — avoid in new code
SELECT
    DepartmentID,
    STUFF((
        SELECT ', ' + LastName
        FROM HR.Employees e2
        WHERE e2.DepartmentID = e1.DepartmentID
        FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),
    1, 2, '') AS EmployeeList
FROM HR.Employees e1
GROUP BY DepartmentID;

-- NEW pattern with STRING_AGG — cleaner, faster, NULL-safe
SELECT
    DepartmentID,
    STRING_AGG(LastName, ', ') WITHIN GROUP (ORDER BY LastName) AS EmployeeList
FROM HR.Employees
GROUP BY DepartmentID;

12. STRING_SPLIT(): Splitting Delimited Strings

STRING_SPLIT(string, separator) is a table-valued function introduced in SQL Server 2016 that splits a delimited string into a single-column table. It pairs naturally with CROSS APPLY to normalise denormalised CSV-like data.

Basic Usage

-- Split a single CSV string into rows
SELECT [value]
FROM STRING_SPLIT(N'Widget,Gadget,Sprocket', ',');
-- value
-- Widget
-- Gadget
-- Sprocket

CROSS APPLY Pattern

-- Normalise a Tags column that stores comma-separated values
SELECT e.EmployeeID, e.Name, s.[value] AS Skill
FROM HR.Employees e
CROSS APPLY STRING_SPLIT(e.Skills, ',') AS s
WHERE LTRIM(RTRIM(s.[value])) <> '';  -- trim whitespace, skip blanks

Ordinal Position (SQL Server 2022)

SQL Server 2022 added a second result column ordinal when you pass enable_ordinal = 1:

-- SQL Server 2022+: get the position of each element
SELECT ordinal, [value]
FROM STRING_SPLIT(N'alpha,beta,gamma,delta', ',', 1)  -- 3rd arg = enable_ordinal
ORDER BY ordinal;
-- ordinal  value
-- 1        alpha
-- 2        beta
-- 3        gamma
-- 4        delta

Filtering with a Whitelist Table

-- Find orders that contain at least one tag from an approved list
DECLARE @AllowedTags TABLE (Tag NVARCHAR(50));
INSERT INTO @AllowedTags VALUES ('bulk'), ('vip'), ('sale');

SELECT DISTINCT o.OrderID
FROM dbo.Orders o
CROSS APPLY OPENJSON(o.Details, '$.tags') AS t
JOIN @AllowedTags a ON a.Tag = t.[value];

13. Modern String Functions: TRIM, CONCAT_WS, TRANSLATE, REPLACE

TRIM() — SQL Server 2017+

SQL Server 2017 added ANSI-standard TRIM() which removes leading and trailing spaces (or a specified set of characters) from a string, replacing the verbose LTRIM(RTRIM(…)) pattern:

-- Remove leading/trailing spaces
SELECT TRIM('   hello world   ');      -- 'hello world'

-- Remove specific characters (SQL Server 2022 enhanced syntax)
SELECT TRIM('.,! ' FROM '...Hello!'); -- 'Hello'

-- Pre-2017 equivalent
SELECT LTRIM(RTRIM('   hello world   '));

CONCAT_WS() — SQL Server 2017+

CONCAT_WS(separator, arg1, arg2, …) concatenates values with a separator, automatically skipping NULL arguments (unlike CONCAT which includes NULLs as empty strings):

-- Build a mailing address, skipping NULL line 2
SELECT CONCAT_WS(', ',
    AddressLine1,
    AddressLine2,   -- NULL rows automatically omitted
    City,
    PostalCode
) AS FullAddress
FROM Person.Address;

TRANSLATE() — SQL Server 2017+

TRANSLATE(string, characters, translations) performs many single-character substitutions in one pass, equivalent to calling REPLACE repeatedly:

-- Replace multiple bracket types with standard parentheses
SELECT TRANSLATE('[2+3]*{4}', '[]{', '()(');
-- Result: (2+3)*(4)

-- Normalise separators in a date string
SELECT TRANSLATE('2026.06.10', '.-/', '---');
-- Result: 2026-06-10

REPLACE Patterns for JSON-Safe Strings

-- Escape single quotes before inserting into dynamic SQL
DECLARE @UserInput NVARCHAR(200) = N'O''Brien';
SET @UserInput = REPLACE(@UserInput, '''', '''''');  -- double the single quotes

-- Remove all non-alphanumeric characters using a helper function pattern
-- (SQL Server lacks regex; TRANSLATE is the closest built-in for char substitution)

14. Sequences: CREATE SEQUENCE, NEXT VALUE FOR, OVER Clause

A SEQUENCE is a schema-bound object that generates a series of numeric values according to a specification. It was introduced in SQL Server 2012 and offers several advantages over the IDENTITY column property.

Creating a Sequence

CREATE SEQUENCE dbo.OrderSeq
    AS INT
    START WITH 1000
    INCREMENT BY 1
    MINVALUE 1000
    MAXVALUE 9999999
    CYCLE                   -- restart from MINVALUE when MAXVALUE is reached
    CACHE 50;               -- pre-allocate 50 values in memory for performance

NEXT VALUE FOR

-- Get the next sequence value
SELECT NEXT VALUE FOR dbo.OrderSeq;   -- returns 1000 on first call

-- Use in an INSERT
INSERT INTO dbo.Orders (OrderID, CustomerID, OrderDate, Details)
VALUES (
    NEXT VALUE FOR dbo.OrderSeq,
    101,
    CAST(GETDATE() AS DATE),
    N'{"product":"NewItem","qty":1,"price":5.00}'
);

-- Use in a DEFAULT constraint (sequence assigned at INSERT time)
ALTER TABLE dbo.Orders
  ADD CONSTRAINT DF_Orders_OrderID
  DEFAULT (NEXT VALUE FOR dbo.OrderSeq) FOR OrderID;

NEXT VALUE FOR with OVER Clause

Uniquely, NEXT VALUE FOR supports an OVER (ORDER BY …) clause, which generates a block of ordered, contiguous sequence values in a single statement:

-- Assign sequence numbers to a staging table in ProductName order
UPDATE s
SET s.SequenceID = NEXT VALUE FOR dbo.OrderSeq OVER (ORDER BY s.ProductName)
FROM dbo.StagingImport s;

Sequence vs IDENTITY — Comparison

15. Practical Use Case: Audit System with Temporal Table + JSON Change Payload

The most powerful pattern combining topics from this article is a self-auditing entity table: a temporal table that captures every row version automatically, plus an AuditLog table that stores a rich JSON diff payload written by an AFTER UPDATE trigger. This gives you both lightweight point-in-time queries (via FOR SYSTEM_TIME) and human-readable change descriptions.

Schema Setup

-- Main entity table — system-versioned
CREATE TABLE dbo.Contracts (
    ContractID   INT           NOT NULL PRIMARY KEY,
    ClientName   NVARCHAR(200) NOT NULL,
    Value        DECIMAL(15,2) NOT NULL,
    Status       NVARCHAR(50)  NOT NULL DEFAULT 'Draft',
    ModifiedBy   NVARCHAR(100) NOT NULL DEFAULT SYSTEM_USER,
    ValidFrom    DATETIME2     GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo      DATETIME2     GENERATED ALWAYS AS ROW END   NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ContractsHistory));

-- Audit log table — stores JSON diff
CREATE TABLE dbo.AuditLog (
    AuditID      INT IDENTITY PRIMARY KEY,
    TableName    NVARCHAR(128) NOT NULL,
    RecordID     INT           NOT NULL,
    ChangedBy    NVARCHAR(100) NOT NULL DEFAULT SYSTEM_USER,
    ChangedAt    DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
    ChangeType   CHAR(1)       NOT NULL,  -- 'I','U','D'
    OldValues    NVARCHAR(MAX) NULL,      -- JSON
    NewValues    NVARCHAR(MAX) NULL       -- JSON
);

Audit Trigger with JSON Diff

CREATE OR ALTER TRIGGER dbo.trg_Contracts_Audit
ON dbo.Contracts
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
        -- UPDATE
        INSERT INTO dbo.AuditLog (TableName, RecordID, ChangeType, OldValues, NewValues)
        SELECT
            'Contracts',
            d.ContractID,
            'U',
            (SELECT d.ContractID AS id, d.ClientName, d.Value, d.Status
             FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
            (SELECT i.ContractID AS id, i.ClientName, i.Value, i.Status
             FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
        FROM deleted d
        JOIN inserted i ON i.ContractID = d.ContractID;
    END
    ELSE IF EXISTS (SELECT 1 FROM inserted)
    BEGIN
        -- INSERT
        INSERT INTO dbo.AuditLog (TableName, RecordID, ChangeType, OldValues, NewValues)
        SELECT 'Contracts', ContractID, 'I', NULL,
               (SELECT ContractID AS id, ClientName, Value, Status
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
        FROM inserted;
    END
    ELSE
    BEGIN
        -- DELETE
        INSERT INTO dbo.AuditLog (TableName, RecordID, ChangeType, OldValues, NewValues)
        SELECT 'Contracts', ContractID, 'D',
               (SELECT ContractID AS id, ClientName, Value, Status
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER), NULL
        FROM deleted;
    END
END;

Querying the Combined Audit Trail

-- Full change history: temporal rows + audit log JSON side by side
SELECT
    al.AuditID,
    al.ChangeType,
    al.ChangedBy,
    al.ChangedAt,
    JSON_VALUE(al.OldValues, '$.Value')  AS OldValue,
    JSON_VALUE(al.NewValues, '$.Value')  AS NewValue,
    JSON_VALUE(al.OldValues, '$.Status') AS OldStatus,
    JSON_VALUE(al.NewValues, '$.Status') AS NewStatus
FROM dbo.AuditLog al
WHERE al.TableName = 'Contracts'
  AND al.RecordID = 1
ORDER BY al.ChangedAt;

-- Cross-check with temporal table: what did the row look like on a specific date?
SELECT ContractID, ClientName, Value, Status, ValidFrom
FROM dbo.Contracts
FOR SYSTEM_TIME AS OF '2026-04-15T00:00:00'
WHERE ContractID = 1;