4. SQL Server Advanced Features and Database-Specific Constructs

This section covers SQL Server-specific features and advanced constructs that leverage the unique capabilities of Microsoft's database platform for enterprise applications.

A. T-SQL Specific Features

1. Common Table Expressions (CTEs) with Recursion

SQL Server's T-SQL provides powerful CTE capabilities, including recursive CTEs for hierarchical data:

-- Recursive CTE for organizational hierarchy
WITH EmployeeHierarchy AS (
    -- Anchor member: Top-level managers
    SELECT employee_id, first_name, last_name, manager_id, 0 AS Level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive member: Direct reports
    SELECT e.employee_id, e.first_name, e.last_name, e.manager_id, eh.Level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT employee_id, REPLICATE('  ', Level) + first_name + ' ' + last_name AS hierarchy
FROM EmployeeHierarchy
ORDER BY Level, employee_id;

2. MERGE Statement for Complex Upsert Operations

The MERGE statement allows for sophisticated data synchronization:

MERGE target_table AS target
USING source_table AS source
ON target.id = source.id
WHEN MATCHED AND target.last_updated < source.last_updated THEN
    UPDATE SET 
        name = source.name,
        email = source.email,
        last_updated = source.last_updated
WHEN NOT MATCHED BY TARGET THEN
    INSERT (id, name, email, last_updated)
    VALUES (source.id, source.name, source.email, source.last_updated)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action, inserted.*, deleted.*;

B. SQL Server Data Types and Features

1. Advanced Data Types

JSON Support:

-- Store and query JSON data
DECLARE @json NVARCHAR(MAX) = '{"name": "John", "skills": ["C#", "SQL", "Azure"]}'

SELECT 
    JSON_VALUE(@json, '$.name') AS Name,
    JSON_QUERY(@json, '$.skills') AS Skills;

-- Query JSON in tables
SELECT customer_id, 
       JSON_VALUE(profile_data, '$.preferences.language') AS PreferredLanguage
FROM customers
WHERE JSON_VALUE(profile_data, '$.status') = 'active';

Spatial Data Types:

-- Working with GEOMETRY data type
DECLARE @point GEOMETRY = GEOMETRY::Point(10, 20, 0);
DECLARE @polygon GEOMETRY = GEOMETRY::STGeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))', 0);

SELECT @point.STWithin(@polygon) AS IsPointInPolygon;

2. Temporal Tables (System-Versioned Tables)

SQL Server provides built-in temporal functionality for tracking data changes:

-- Create temporal table
CREATE TABLE dbo.Employee
(
    EmployeeID INT IDENTITY PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Position NVARCHAR(100) NOT NULL,
    Salary DECIMAL(10,2) NOT NULL,
    
    -- System temporal columns
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

-- Query historical data
SELECT * FROM dbo.Employee 
FOR SYSTEM_TIME AS OF '2024-01-01 12:00:00';

-- Query all changes between dates
SELECT * FROM dbo.Employee 
FOR SYSTEM_TIME BETWEEN '2024-01-01' AND '2024-01-31';

C. Performance and Optimization Features

1. Columnstore Indexes

Ideal for analytical workloads and data warehousing:

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

-- Create nonclustered columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_ProductAnalysis 
ON dbo.Sales (ProductID, SaleDate, Quantity, Revenue);

-- Query with columnstore benefits
SELECT ProductID, YEAR(SaleDate) AS SaleYear, SUM(Revenue) AS TotalRevenue
FROM dbo.Sales
WHERE SaleDate >= '2023-01-01'
GROUP BY ProductID, YEAR(SaleDate);

2. Memory-Optimized Tables (In-Memory OLTP)

For high-performance transactional workloads:

-- Create memory-optimized table
CREATE TABLE dbo.SessionData
(
    SessionID UNIQUEIDENTIFIER PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),
    UserID INT NOT NULL,
    StartTime DATETIME2 NOT NULL,
    LastActivity DATETIME2 NOT NULL,
    
    INDEX IX_UserID NONCLUSTERED (UserID)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

-- Natively compiled stored procedure
CREATE PROCEDURE dbo.UpdateSessionActivity
    @SessionID UNIQUEIDENTIFIER,
    @NewActivity DATETIME2
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH
(
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'us_english'
)
    UPDATE dbo.SessionData 
    SET LastActivity = @NewActivity 
    WHERE SessionID = @SessionID;
END;

D. Security and Encryption Features

1. Always Encrypted

Protects sensitive data with client-side encryption:

-- Create column master key
CREATE COLUMN MASTER KEY CMK_Auto1
WITH
(
    KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = 'CurrentUser/My/f2260f28d909d21c642a3d8e0b45a830e79a1420'
);

-- Create table with encrypted columns
CREATE TABLE dbo.Patients
(
    PatientID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(50) NULL,
    SSN CHAR(11) COLLATE Latin1_General_BIN2 ENCRYPTED WITH 
    (
        COLUMN ENCRYPTION KEY = CEK_Auto1,
        ENCRYPTION_TYPE = Deterministic,
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
    ),
    BirthDate DATE ENCRYPTED WITH 
    (
        COLUMN ENCRYPTION KEY = CEK_Auto1,
        ENCRYPTION_TYPE = Randomized,
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
    )
);

2. Row-Level Security (RLS)

Implements fine-grained access control:

-- Create security function
CREATE FUNCTION dbo.fn_SecurityPredicateCustomers(@CustomerRegion AS NVARCHAR(50))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_SecurityPredicateCustomers_result
    WHERE @CustomerRegion = USER_NAME() OR USER_NAME() = 'Manager';

-- Apply security policy
CREATE SECURITY POLICY dbo.CustomerSecurityPolicy
    ADD FILTER PREDICATE dbo.fn_SecurityPredicateCustomers(Region) ON dbo.Customers,
    ADD BLOCK PREDICATE dbo.fn_SecurityPredicateCustomers(Region) ON dbo.Customers AFTER UPDATE
WITH (STATE = ON);

E. Advanced Analytics and BI Features

1. SQL Server Analysis Services Integration

Query multidimensional data directly from T-SQL:

-- Query Analysis Services cube using OPENROWSET
SELECT *
FROM OPENROWSET('MSOLAP',
    'DATA SOURCE=localhost;INITIAL CATALOG=AdventureWorksDW;',
    'SELECT [Measures].[Internet Sales Amount] ON COLUMNS,
     [Product].[Category].MEMBERS ON ROWS
     FROM [Adventure Works]')

2. Machine Learning Services (R/Python Integration)

Execute R or Python scripts within SQL Server:

-- Execute R script for statistical analysis
EXEC sp_execute_external_script
    @language = N'R',
    @script = N'
        model <- lm(SalesAmount ~ ProductCost + MarketingSpend, data = InputDataSet)
        predictions <- predict(model, InputDataSet)
        OutputDataSet <- data.frame(InputDataSet, Predicted = predictions)
    ',
    @input_data_1 = N'SELECT SalesAmount, ProductCost, MarketingSpend FROM dbo.SalesData',
    @input_data_1_name = N'InputDataSet',
    @output_data_1_name = N'OutputDataSet'
WITH RESULT SETS ((
    SalesAmount DECIMAL(10,2),
    ProductCost DECIMAL(10,2),
    MarketingSpend DECIMAL(10,2),
    Predicted DECIMAL(10,2)
));

F. Modern SQL Server Features

1. Graph Database Capabilities

Model and query graph relationships:

-- Create node tables
CREATE TABLE Person (
    ID INTEGER PRIMARY KEY,
    Name NVARCHAR(100)
) AS NODE;

-- Create edge table
CREATE TABLE Friends AS EDGE;

-- Insert data
INSERT INTO Person VALUES (1, 'Alice'), (2, 'John'), (3, 'Bob');
INSERT INTO Friends VALUES ((SELECT $node_id FROM Person WHERE Name = 'Alice'),
                           (SELECT $node_id FROM Person WHERE Name = 'John'));

-- Query graph relationships
SELECT Person2.Name AS FriendOfFriend
FROM Person Person1, Friends, Person Person2, Friends AS Friends2, Person Person3
WHERE MATCH(Person1-(Friends)->Person2-(Friends2)->Person3)
  AND Person1.Name = 'Alice'
  AND Person3.Name <> 'Alice';

2. Intelligent Query Processing

SQL Server automatically optimizes queries with features like:

• Batch Mode on Rowstore: Automatically enabled for eligible queries
• Memory Grant Feedback: Adjusts memory grants based on execution history
• Interleaved Execution: Optimizes multi-statement table-valued functions

-- Enable intelligent query processing features
ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 160;

-- Query that benefits from intelligent query processing
SELECT c.CustomerName, SUM(o.OrderValue) as TotalOrders
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderDate >= DATEADD(year, -1, GETDATE())
GROUP BY c.CustomerName
HAVING SUM(o.OrderValue) > 10000
ORDER BY TotalOrders DESC;