Modern SQL Server Features: JSON, Temporal Tables, and In-Memory OLTP

Modern SQL Server Features: JSON, Temporal Tables, and In-Memory OLTP

Introduction

Modern SQL Server provides advanced features for diverse workloads. This guide covers JSON and XML support for semi-structured data, temporal tables for automatic history tracking, in-memory OLTP for extreme performance, columnstore indexes for analytics, and graph databases for relationship modeling.

JSON Support

Storing and Querying JSON

-- Create table with JSON column
CREATE TABLE dbo.Products (
    ProductID INT PRIMARY KEY IDENTITY,
    ProductName NVARCHAR(100),
    Price DECIMAL(10,2),
    Attributes NVARCHAR(MAX)  -- JSON data
);

-- Insert JSON data
INSERT INTO dbo.Products (ProductName, Price, Attributes)
VALUES 
    ('Laptop', 1299.99, '{"brand":"Dell","processor":"Intel i7","ram":"16GB","storage":"512GB SSD"}'),
    ('Mouse', 29.99, '{"brand":"Logitech","wireless":true,"dpi":1600}'),
    ('Monitor', 349.99, '{"brand":"Samsung","size":"27 inch","resolution":"2560x1440","refresh_rate":144}');

Extracting JSON Values

-- JSON_VALUE: Extract scalar values
SELECT 
    ProductID,
    ProductName,
    JSON_VALUE(Attributes, '$.brand') AS Brand,
    JSON_VALUE(Attributes, '$.processor') AS Processor,
    JSON_VALUE(Attributes, '$.ram') AS RAM
FROM dbo.Products
WHERE JSON_VALUE(Attributes, '$.brand') = 'Dell';

-- JSON_QUERY: Extract objects or arrays
SELECT 
    ProductID,
    ProductName,
    JSON_QUERY(Attributes, '$') AS FullAttributes
FROM dbo.Products;

Modifying JSON Data

-- JSON_MODIFY: Update JSON properties
UPDATE dbo.Products
SET Attributes = JSON_MODIFY(Attributes, '$.price_updated', CAST(GETDATE() AS VARCHAR(50)))
WHERE ProductID = 1;

-- Add new property
UPDATE dbo.Products
SET Attributes = JSON_MODIFY(Attributes, '$.warranty', '2 years')
WHERE JSON_VALUE(Attributes, '$.brand') = 'Dell';

-- Remove property (set to NULL)
UPDATE dbo.Products
SET Attributes = JSON_MODIFY(Attributes, '$.temp_field', NULL)
WHERE ProductID = 1;

Converting JSON to Relational

-- OPENJSON: Parse JSON into table format
DECLARE @json NVARCHAR(MAX) = N'[
    {"id":1,"name":"John","age":30},
    {"id":2,"name":"Jane","age":25},
    {"id":3,"name":"Bob","age":35}
]';

SELECT *
FROM OPENJSON(@json)
WITH (
    id INT '$.id',
    name NVARCHAR(50) '$.name',
    age INT '$.age'
);

-- Parse nested JSON
DECLARE @nestedJson NVARCHAR(MAX) = N'{
    "customer": {
        "id": 1001,
        "name": "Contoso",
        "address": {
            "street": "123 Main St",
            "city": "Seattle",
            "zip": "98101"
        }
    }
}';

SELECT 
    JSON_VALUE(@nestedJson, '$.customer.id') AS CustomerID,
    JSON_VALUE(@nestedJson, '$.customer.name') AS CustomerName,
    JSON_VALUE(@nestedJson, '$.customer.address.city') AS City;

FOR JSON: Convert to JSON

-- FOR JSON AUTO: Automatic structure
SELECT 
    c.CustomerID,
    c.CustomerName,
    o.OrderID,
    o.OrderDate,
    o.TotalAmount
FROM dbo.Customers c
INNER JOIN dbo.Orders o ON c.CustomerID = o.CustomerID
WHERE c.CustomerID = 1001
FOR JSON AUTO;

-- Result: [{"CustomerID":1001,"CustomerName":"Contoso","o":[{"OrderID":100,"OrderDate":"2025-01-15","TotalAmount":299.99}]}]

-- FOR JSON PATH: Custom structure
SELECT 
    CustomerID AS 'customer.id',
    CustomerName AS 'customer.name',
    Email AS 'customer.email',
    OrderID AS 'orders.id',
    OrderDate AS 'orders.date',
    TotalAmount AS 'orders.total'
FROM dbo.Customers c
INNER JOIN dbo.Orders o ON c.CustomerID = o.CustomerID
WHERE c.CustomerID = 1001
FOR JSON PATH, ROOT('data');

-- Result: {"data":[{"customer":{"id":1001,"name":"Contoso","email":"contact@contoso.com"},"orders":{"id":100,"date":"2025-01-15","total":299.99}}]}

Indexing JSON Data

-- Create computed column for JSON property
ALTER TABLE dbo.Products
ADD Brand AS JSON_VALUE(Attributes, '$.brand') PERSISTED;

-- Create index on computed column
CREATE INDEX IX_Products_Brand ON dbo.Products(Brand);

-- Query uses index
SELECT ProductID, ProductName, Brand
FROM dbo.Products
WHERE Brand = 'Dell';

REST API Integration Example

-- Stored procedure to return JSON for REST API
CREATE PROCEDURE dbo.GetProductsJSON
    @CategoryID INT = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        ProductID AS id,
        ProductName AS name,
        Price AS price,
        JSON_QUERY(Attributes) AS attributes
    FROM dbo.Products
    WHERE @CategoryID IS NULL OR CategoryID = @CategoryID
    FOR JSON PATH;
END;
GO

-- Call from application
-- HTTP GET /api/products?categoryId=5
-- Returns: [{"id":1,"name":"Laptop","price":1299.99,"attributes":{"brand":"Dell",...}}]

XML Support

Storing and Querying XML

-- Create table with XML column
CREATE TABLE dbo.ProductsXML (
    ProductID INT PRIMARY KEY IDENTITY,
    ProductName NVARCHAR(100),
    ProductData XML
);

-- Insert XML data
INSERT INTO dbo.ProductsXML (ProductName, ProductData)
VALUES 
    ('Laptop', '<Product><Brand>Dell</Brand><Processor>Intel i7</Processor><RAM>16GB</RAM></Product>'),
    ('Mouse', '<Product><Brand>Logitech</Brand><Wireless>true</Wireless><DPI>1600</DPI></Product>');

-- XQuery: Extract values
SELECT 
    ProductID,
    ProductName,
    ProductData.value('(/Product/Brand)[1]', 'NVARCHAR(50)') AS Brand,
    ProductData.value('(/Product/Processor)[1]', 'NVARCHAR(50)') AS Processor
FROM dbo.ProductsXML;

-- XQuery: Check existence
SELECT *
FROM dbo.ProductsXML
WHERE ProductData.exist('/Product/Wireless[. = "true"]') = 1;

FOR XML

-- FOR XML RAW
SELECT TOP 3 ProductID, ProductName, Price
FROM dbo.Products
FOR XML RAW('Product'), ROOT('Products'), ELEMENTS;

-- Result:
-- <Products>
--   <Product><ProductID>1</ProductID><ProductName>Laptop</ProductName><Price>1299.99</Price></Product>
--   <Product><ProductID>2</ProductID><ProductName>Mouse</ProductName><Price>29.99</Price></Product>
-- </Products>

-- FOR XML PATH (custom structure)
SELECT 
    CustomerID AS '@id',
    CustomerName AS 'Name',
    Email AS 'Contact/Email',
    Phone AS 'Contact/Phone'
FROM dbo.Customers
WHERE CustomerID = 1001
FOR XML PATH('Customer'), ROOT('Customers');

Temporal Tables

Creating System-Versioned Temporal Table

-- Create temporal table
CREATE TABLE dbo.Employees (
    EmployeeID INT PRIMARY KEY NOT NULL,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Department NVARCHAR(50) NOT NULL,
    Salary DECIMAL(10,2) NOT NULL,
    
    -- Period columns (auto-managed by SQL Server)
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeesHistory));

-- Insert data
INSERT INTO dbo.Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES 
    (1, 'John', 'Doe', 'IT', 75000),
    (2, 'Jane', 'Smith', 'Sales', 65000);

-- Update employee (creates history record)
UPDATE dbo.Employees
SET Salary = 80000, Department = 'Engineering'
WHERE EmployeeID = 1;

-- Delete employee (moves to history)
DELETE FROM dbo.Employees
WHERE EmployeeID = 2;

Querying Temporal Data

-- Current data (default)
SELECT * FROM dbo.Employees;

-- All historical versions
SELECT * FROM dbo.Employees
FOR SYSTEM_TIME ALL
ORDER BY EmployeeID, SysStartTime;

-- Point-in-time query (as of specific date)
SELECT * FROM dbo.Employees
FOR SYSTEM_TIME AS OF '2025-01-01 12:00:00'
WHERE EmployeeID = 1;

-- Changes between two dates
SELECT * FROM dbo.Employees
FOR SYSTEM_TIME BETWEEN '2025-01-01' AND '2025-12-31'
WHERE EmployeeID = 1
ORDER BY SysStartTime;

-- Changes from specific time to now
SELECT * FROM dbo.Employees
FOR SYSTEM_TIME FROM '2025-06-01' TO '9999-12-31 23:59:59.9999999'
WHERE EmployeeID = 1;

Audit Trail Example

-- Find who changed salary and when
SELECT 
    EmployeeID,
    FirstName,
    LastName,
    Salary,
    Department,
    SysStartTime AS ChangeDate,
    SysEndTime AS ValidUntil,
    CASE 
        WHEN SysEndTime = '9999-12-31 23:59:59.9999999' THEN 'Current'
        ELSE 'Historical'
    END AS Status
FROM dbo.Employees
FOR SYSTEM_TIME ALL
WHERE EmployeeID = 1
ORDER BY SysStartTime;

-- Salary history report
SELECT 
    e.EmployeeID,
    e.FirstName + ' ' + e.LastName AS FullName,
    e.Salary AS OldSalary,
    LEAD(e.Salary) OVER (PARTITION BY e.EmployeeID ORDER BY e.SysStartTime) AS NewSalary,
    e.SysStartTime AS ChangeDate,
    DATEDIFF(DAY, e.SysStartTime, e.SysEndTime) AS DaysActive
FROM dbo.Employees FOR SYSTEM_TIME ALL e
WHERE e.EmployeeID = 1
ORDER BY e.SysStartTime;

Retention Policy

-- Enable retention policy (keep 6 months of history)
ALTER TABLE dbo.Employees
SET (SYSTEM_VERSIONING = OFF);

ALTER TABLE dbo.EmployeesHistory
SET (DATA_DELETION = ON (FILTER_COLUMN = SysEndTime, RETENTION_PERIOD = 6 MONTHS));

ALTER TABLE dbo.Employees
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeesHistory, DATA_CONSISTENCY_CHECK = ON));

In-Memory OLTP

Creating Memory-Optimized Tables

-- Add memory-optimized filegroup (one-time setup)
ALTER DATABASE AdventureWorks
ADD FILEGROUP MemoryOptimized_FG CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE AdventureWorks
ADD FILE (
    NAME = 'MemoryOptimized_File',
    FILENAME = 'C:\Data\AdventureWorks_MemoryOptimized'
) TO FILEGROUP MemoryOptimized_FG;

-- Create memory-optimized table
CREATE TABLE dbo.OrdersMemoryOptimized (
    OrderID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),
    CustomerID INT NOT NULL,
    OrderDate DATETIME2 NOT NULL,
    TotalAmount DECIMAL(10,2) NOT NULL,
    Status NVARCHAR(20) NOT NULL,
    
    INDEX IX_CustomerID NONCLUSTERED (CustomerID),
    INDEX IX_OrderDate NONCLUSTERED (OrderDate)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

-- DURABILITY options:
-- SCHEMA_AND_DATA: Full durability (logged)
-- SCHEMA_ONLY: Data lost on restart (faster)

Natively Compiled Stored Procedures

-- Create natively compiled procedure
CREATE PROCEDURE dbo.InsertOrder_Native
    @CustomerID INT,
    @OrderDate DATETIME2,
    @TotalAmount DECIMAL(10,2),
    @Status NVARCHAR(20)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'us_english'
)
    INSERT INTO dbo.OrdersMemoryOptimized (OrderID, CustomerID, OrderDate, TotalAmount, Status)
    VALUES (NEXT VALUE FOR dbo.OrderIDSequence, @CustomerID, @OrderDate, @TotalAmount, @Status);
END;
GO

-- Create sequence for OrderID
CREATE SEQUENCE dbo.OrderIDSequence
START WITH 1
INCREMENT BY 1;

Hash vs Range Indexes

-- Hash index: Best for equality searches
-- BUCKET_COUNT should be 1-2x row count
CREATE TABLE dbo.CustomersMemoryOptimized (
    CustomerID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),
    CustomerName NVARCHAR(100),
    Email NVARCHAR(100)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

-- Range index: Best for range queries, sorting
CREATE TABLE dbo.ProductsMemoryOptimized (
    ProductID INT NOT NULL PRIMARY KEY NONCLUSTERED,  -- Range index
    ProductName NVARCHAR(100),
    Price DECIMAL(10,2),
    
    INDEX IX_Price NONCLUSTERED (Price)  -- Range queries
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Monitoring Memory Usage

-- Memory usage by table
SELECT 
    OBJECT_NAME(object_id) AS TableName,
    memory_allocated_for_table_kb / 1024 AS table_memory_mb,
    memory_allocated_for_indexes_kb / 1024 AS index_memory_mb,
    (memory_allocated_for_table_kb + memory_allocated_for_indexes_kb) / 1024 AS total_memory_mb
FROM sys.dm_db_xtp_table_memory_stats
ORDER BY total_memory_mb DESC;

-- Total memory usage
SELECT 
    SUM(allocated_bytes) / 1048576 AS allocated_mb,
    SUM(used_bytes) / 1048576 AS used_mb
FROM sys.dm_db_xtp_memory_consumers;

Columnstore Indexes

Clustered Columnstore Index

-- Create fact table with clustered columnstore
CREATE TABLE dbo.FactSales (
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    CustomerID INT NOT NULL,
    OrderDate DATE NOT NULL,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(10,2) NOT NULL,
    TotalAmount DECIMAL(10,2) NOT NULL
);

-- Create clustered columnstore index
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales ON dbo.FactSales;

-- Insert data (batch mode execution)
INSERT INTO dbo.FactSales
SELECT 
    OrderID,
    ProductID,
    CustomerID,
    CAST(OrderDate AS DATE),
    Quantity,
    UnitPrice,
    TotalAmount
FROM dbo.Orders o
INNER JOIN dbo.OrderDetails od ON o.OrderID = od.OrderID;

-- Query benefits from batch mode and compression
SELECT 
    YEAR(OrderDate) AS OrderYear,
    MONTH(OrderDate) AS OrderMonth,
    SUM(TotalAmount) AS Revenue,
    COUNT(*) AS OrderCount
FROM dbo.FactSales
WHERE OrderDate >= '2024-01-01'
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY OrderYear, OrderMonth;

Nonclustered Columnstore Index

-- Add columnstore to existing OLTP table
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Orders_Analytics
ON dbo.Orders (OrderDate, CustomerID, TotalAmount, Status)
WHERE OrderDate >= '2024-01-01';  -- Filtered

-- Real-time operational analytics
SELECT 
    CustomerID,
    COUNT(*) AS OrderCount,
    SUM(TotalAmount) AS TotalRevenue
FROM dbo.Orders
WHERE OrderDate >= '2024-01-01'
GROUP BY CustomerID
ORDER BY TotalRevenue DESC;

Columnstore Performance

-- View columnstore segments and compression
SELECT 
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    p.partition_number,
    p.rows AS row_count,
    p.data_compression_desc,
    css.segment_id,
    css.column_id,
    css.row_count AS segment_rows,
    css.on_disk_size / 1024 AS on_disk_kb,
    css.on_disk_size * 100.0 / NULLIF(SUM(css.on_disk_size) OVER(PARTITION BY i.object_id), 0) AS pct_of_index
FROM sys.indexes i
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.column_store_segments css ON p.partition_id = css.partition_id
WHERE i.type IN (5, 6)  -- Columnstore indexes
ORDER BY TableName, segment_id, column_id;

Graph Databases

Creating Graph Tables

-- Create node tables
CREATE TABLE dbo.Person (
    PersonID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100)
) AS NODE;

CREATE TABLE dbo.Restaurant (
    RestaurantID INT PRIMARY KEY,
    Name NVARCHAR(100),
    City NVARCHAR(50),
    Rating DECIMAL(2,1)
) AS NODE;

-- Create edge table
CREATE TABLE dbo.Likes AS EDGE;

-- Insert nodes
INSERT INTO dbo.Person (PersonID, FirstName, LastName, Email)
VALUES 
    (1, 'John', 'Doe', 'john@example.com'),
    (2, 'Jane', 'Smith', 'jane@example.com'),
    (3, 'Bob', 'Johnson', 'bob@example.com');

INSERT INTO dbo.Restaurant (RestaurantID, Name, City, Rating)
VALUES 
    (100, 'Pizza Palace', 'Seattle', 4.5),
    (101, 'Burger Barn', 'Seattle', 4.0),
    (102, 'Sushi Studio', 'Portland', 4.8);

-- Insert edges (relationships)
INSERT INTO dbo.Likes ($from_id, $to_id)
VALUES 
    ((SELECT $node_id FROM dbo.Person WHERE PersonID = 1), 
     (SELECT $node_id FROM dbo.Restaurant WHERE RestaurantID = 100)),
    ((SELECT $node_id FROM dbo.Person WHERE PersonID = 1), 
     (SELECT $node_id FROM dbo.Restaurant WHERE RestaurantID = 102)),
    ((SELECT $node_id FROM dbo.Person WHERE PersonID = 2), 
     (SELECT $node_id FROM dbo.Restaurant WHERE RestaurantID = 100));

Querying Graph Data

-- MATCH: Find relationships
SELECT 
    p.FirstName + ' ' + p.LastName AS Person,
    r.Name AS Restaurant,
    r.Rating
FROM dbo.Person p, dbo.Likes, dbo.Restaurant r
WHERE MATCH(p-(Likes)->r)
    AND p.PersonID = 1;

-- Find common likes (friends who like same restaurant)
SELECT 
    p1.FirstName + ' ' + p1.LastName AS Person1,
    p2.FirstName + ' ' + p2.LastName AS Person2,
    r.Name AS CommonRestaurant
FROM dbo.Person p1, dbo.Likes l1, dbo.Restaurant r, dbo.Likes l2, dbo.Person p2
WHERE MATCH(p1-(l1)->r<-(l2)-p2)
    AND p1.PersonID < p2.PersonID;  -- Avoid duplicates

-- Shortest path (requires SQL Server 2019+)
SELECT PersonName, RestaurantName
FROM (
    SELECT 
        p.FirstName + ' ' + p.LastName AS PersonName,
        STRING_AGG(r.Name, ' -> ') WITHIN GROUP (GRAPH PATH) AS RestaurantName,
        LAST_VALUE(r.Name) WITHIN GROUP (GRAPH PATH) AS LastRestaurant
    FROM dbo.Person p, dbo.Likes FOR PATH, dbo.Restaurant r FOR PATH
    WHERE MATCH(SHORTEST_PATH(p(-(Likes)->r)+))
        AND p.PersonID = 1
) AS Results;

Social Network Example

-- Create friendship edge
CREATE TABLE dbo.FriendOf AS EDGE;

-- Insert friendships
INSERT INTO dbo.FriendOf ($from_id, $to_id)
VALUES 
    ((SELECT $node_id FROM dbo.Person WHERE PersonID = 1), 
     (SELECT $node_id FROM dbo.Person WHERE PersonID = 2)),
    ((SELECT $node_id FROM dbo.Person WHERE PersonID = 2), 
     (SELECT $node_id FROM dbo.Person WHERE PersonID = 3));

-- Find friends-of-friends
SELECT 
    p1.FirstName + ' ' + p1.LastName AS Person,
    p3.FirstName + ' ' + p3.LastName AS FriendOfFriend
FROM dbo.Person p1, dbo.FriendOf f1, dbo.Person p2, dbo.FriendOf f2, dbo.Person p3
WHERE MATCH(p1-(f1)->p2-(f2)->p3)
    AND p1.PersonID = 1
    AND p1.PersonID <> p3.PersonID;  -- Exclude self

Key Takeaways

• JSON support enables semi-structured data storage and REST APIs
• XML provides enterprise data exchange capabilities
• Temporal tables automatically track data history for auditing
• In-memory OLTP delivers extreme performance for high-concurrency workloads
• Columnstore indexes optimize analytics on large datasets
• Graph databases model complex relationships efficiently
• Use computed columns to index JSON/XML properties
• Choose SCHEMA_AND_DATA durability for mission-critical in-memory tables
• Columnstore compression can reduce storage by 10x
• Graph queries simplify relationship traversal

Next Steps

• Implement JSON APIs for modern applications
• Enable temporal tables for audit-critical tables
• Evaluate in-memory OLTP for high-throughput scenarios
• Add columnstore indexes to fact tables
• Model relationships with graph tables
• Monitor memory usage for in-memory objects

Additional Resources

• JSON Support
• Temporal Tables
• In-Memory OLTP
• Columnstore Indexes
• Graph Databases

---

Innovate. Optimize. Scale. Transform.