Database Sql Server 2016

Key Features

Query Store (New in 2016)

Query Store is the single most important feature in SQL Server 2016. It captures query text, execution plans, and runtime statistics persistently (survives restarts).

Enable and configure:

ALTER DATABASE [MyDB] SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60,
    MAX_STORAGE_SIZE_MB = 1024,
    QUERY_CAPTURE_MODE = AUTO,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 200
);

Important 2016 limitations:

• No wait stats capture (added in 2017)
• No Query Store hints (added in 2022)
• QUERY_CAPTURE_MODE = CUSTOM not available (added in 2019)
• Can transition to READ_ONLY under space pressure -- monitor with:

SELECT actual_state_desc, readonly_reason
FROM sys.database_query_store_options;

Force a plan to fix parameter sniffing:

EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 7;

Temporal Tables

System-versioned temporal tables automatically track data changes over time.

CREATE TABLE dbo.Employee (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Department NVARCHAR(50),
    Salary DECIMAL(18,2),
    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.EmployeeHistory));

-- Query as of a point in time
SELECT * FROM dbo.Employee FOR SYSTEM_TIME AS OF '2024-01-15T10:00:00';

-- Query change history for a specific row
SELECT * FROM dbo.Employee FOR SYSTEM_TIME ALL
WHERE EmployeeID = 42
ORDER BY ValidFrom;

Pitfalls:

• History table grows indefinitely -- implement a retention cleanup strategy
• Schema changes require temporarily disabling versioning
• Cannot use TRUNCATE on temporal tables
• Foreign keys referencing temporal tables have limitations

Row-Level Security (RLS)

Filter predicates control which rows users can see:

CREATE FUNCTION dbo.fn_SecurityPredicate(@TenantId INT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN SELECT 1 AS result WHERE @TenantId = CAST(SESSION_CONTEXT(N'TenantId') AS INT);

CREATE SECURITY POLICY dbo.TenantFilter
ADD FILTER PREDICATE dbo.fn_SecurityPredicate(TenantId) ON dbo.Orders,
ADD BLOCK PREDICATE dbo.fn_SecurityPredicate(TenantId) ON dbo.Orders;

-- Set context per session
EXEC sp_set_session_context @key = N'TenantId', @value = 42;

Performance note: The filter predicate function is inlined into every query. Keep it simple -- complex predicates degrade performance.

Always Encrypted

Client-side encryption for sensitive columns. The database engine never sees plaintext.

Two encryption types:

• Deterministic -- Same plaintext always produces same ciphertext. Allows equality comparisons, joins, GROUP BY, indexing.
• Randomized -- Different ciphertext each time. No query operations on encrypted data.

Limitations in 2016:

• No enclave computations (added in 2019 with secure enclaves)
• Cannot use encrypted columns in: LIKE, range comparisons, ORDER BY, UNION, CASE
• Requires updated client drivers (ODBC 17+, .NET 4.6+)

Dynamic Data Masking

Obfuscates data in query results without changing stored data:

ALTER TABLE dbo.Customer
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');
ALTER TABLE dbo.Customer
ALTER COLUMN SSN ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)');
ALTER TABLE dbo.Customer
ALTER COLUMN CreditCard ADD MASKED WITH (FUNCTION = 'default()');

-- Users with UNMASK permission see real values
GRANT UNMASK TO [ReportUser];

Warning: DDM is not a security boundary. Users with sufficient privileges or determined attackers can infer values. Use Always Encrypted for true security.

JSON Support

SQL Server 2016 added JSON functions -- no native JSON data type, but functions work on NVARCHAR columns:

-- Parse JSON
SELECT JSON_VALUE(@json, '$.name') AS name;
SELECT JSON_QUERY(@json, '$.address') AS address_object;

-- Check validity
SELECT ISJSON(@json);

-- Transform relational to JSON
SELECT EmployeeID, Name, Department
FROM dbo.Employee
FOR JSON PATH, ROOT('employees');

-- Shred JSON into relational rows
SELECT * FROM OPENJSON(@json)
WITH (name NVARCHAR(100), age INT, city NVARCHAR(50) '$.address.city');

2016 limitations:

• No native JSON data type (stored as NVARCHAR)
• No JSON index (added in 2025)
• No JSON_CONTAINS or JSON_ARRAY_APPEND

R Services

Execute R scripts inside the SQL Server engine:

EXEC sp_execute_external_script
    @language = N'R',
    @script = N'OutputDataSet <- InputDataSet;
                OutputDataSet$Prediction <- predict(model, InputDataSet);',
    @input_data_1 = N'SELECT * FROM dbo.ScoringData';

Requires: sp_configure 'external scripts enabled', 1 and Launchpad service running.

2016 limitation: R only. Python added in 2017.

PolyBase

Query external data sources (Hadoop, Azure Blob) using T-SQL:

CREATE EXTERNAL DATA SOURCE MyHadoop
WITH (TYPE = HADOOP, LOCATION = 'hdfs://namenode:8020');

CREATE EXTERNAL FILE FORMAT CsvFormat
WITH (FORMAT_TYPE = DELIMITEDTEXT, FORMAT_OPTIONS (FIELD_TERMINATOR = ','));

CREATE EXTERNAL TABLE dbo.ExternalData (...)
WITH (LOCATION = '/data/files/', DATA_SOURCE = MyHadoop, FILE_FORMAT = CsvFormat);

SELECT * FROM dbo.ExternalData WHERE Year = 2024;  -- Pushes predicate to Hadoop

2016 scope: Hadoop and Azure Blob only. SQL Server, Oracle, and other RDBMS sources added in 2019.

Performance Improvements in 2016

• Batch mode for columnstore improvements (adaptive join not yet available)
• Live query statistics -- View executing query plans in real time via SSMS
• Parallel INSERT...SELECT -- Parallel execution for bulk inserts
• Trace flag 2371 as default -- Auto-stats update threshold is now dynamic for large tables (no longer need TF 2371)
• tempdb improvements -- Mixed extent allocation eliminated (no need for TF 1118)
• In-Memory OLTP v2 -- Removed many limitations from 2014 (ALTER TABLE, parallel plans, OUTER JOIN support)

Version Boundaries

• This agent covers SQL Server 2016 (compat level 130) specifically
• Features NOT available in 2016 (introduced later):
  • Adaptive query processing / Intelligent QP (2017/2019)
  • Wait stats in Query Store (2017)
  • Resumable online index rebuild (2017)
  • Graph database support (2017)
  • Python in ML Services (2017)
  • Accelerated Database Recovery (2019)
  • Scalar UDF inlining (2019)
  • Batch mode on rowstore (2019)
  • Parameter Sensitive Plan optimization (2022)
  • Query Store hints (2022)
  • Ledger tables (2022)

Common Pitfalls

1. Query Store going READ_ONLY -- Monitor sys.database_query_store_options. Increase MAX_STORAGE_SIZE_MB and ensure SIZE_BASED_CLEANUP_MODE = AUTO.
2. Temporal table history bloat -- No built-in retention policy in 2016 (added in 2017). Implement manual cleanup.
3. RLS performance -- Inline TVFs used as filter predicates add overhead to every query. Test with realistic data volumes.
4. Stretch Database deprecation -- Stretch Database was deprecated in SQL Server 2022 and removed in 2025. Do not build new solutions on it.
5. PolyBase Java dependency -- PolyBase in 2016 requires Java Runtime (JRE 7+). Ensure it is maintained and patched.
6. Extended support ending soon -- Extended support ends July 2026. Begin migration planning now.

Migration from SQL Server 2014

When upgrading from SQL Server 2014 (compat level 120) to 2016 (compat level 130):

1. New cardinality estimator -- Already available in 2014 at compat 120, but 2016 adds trace flag 2312 for additional CE fixes
2. Enable Query Store -- First action after upgrade. Capture baseline before changing compat level.
3. Upgrade strategy:
   • Upgrade the engine first (keep compat level at 120)
   • Enable Query Store to capture baseline performance
   • Change compat level to 130
   • Monitor Query Store for regressions
   • Force previous plans for any regressed queries
4. tempdb: Remove trace flags 1117 and 1118 -- their behavior is now default
5. Test for behavioral changes in string comparisons and datetime conversions at compat 130

Reference Files

Load these for deep knowledge:

• ../references/architecture.md -- Storage engine, buffer pool, query processing
• ../references/diagnostics.md -- Wait stats, DMVs, Query Store usage, Extended Events
• ../references/best-practices.md -- Instance configuration, backup strategy, security