SQL Server 2025 Expert

Key Features

Native Vector Data Type and DiskANN Indexes

SQL Server 2025 introduces a native VECTOR data type and vector indexes based on the DiskANN algorithm for approximate nearest neighbor (ANN) search.

-- Create a table with a vector column
CREATE TABLE dbo.Documents (
    DocumentID INT PRIMARY KEY,
    Title NVARCHAR(200),
    Content NVARCHAR(MAX),
    Embedding VECTOR(1536) NOT NULL  -- 1536 dimensions (OpenAI ada-002 size)
);

-- Insert vector data
INSERT INTO dbo.Documents (DocumentID, Title, Content, Embedding)
VALUES (1, 'SQL Server Guide', 'Content here...',
        CAST('[0.1, 0.2, 0.3, ...]' AS VECTOR(1536)));

-- Create a DiskANN vector index
CREATE VECTOR INDEX IX_Documents_Embedding
ON dbo.Documents(Embedding)
WITH (METRIC = 'cosine', TYPE = 'DISKANN');
-- Supported metrics: cosine, dot_product, euclidean

-- Perform vector similarity search
SELECT TOP 10 DocumentID, Title,
       VECTOR_DISTANCE('cosine', Embedding, @query_vector) AS distance
FROM dbo.Documents
ORDER BY VECTOR_DISTANCE('cosine', Embedding, @query_vector);

Key functions:

• VECTOR_DISTANCE(metric, vector1, vector2) -- Calculate distance between vectors
• CAST(json_array AS VECTOR(n)) -- Convert JSON array to vector
• DiskANN indexes support DML operations -- the index updates automatically with inserts/updates/deletes

Design considerations:

• Vector dimensions impact storage and index size significantly
• DiskANN indexes are disk-based, suitable for large-scale vector datasets
• Combine vector search with traditional WHERE filters for hybrid search
• Consider partitioning large vector tables for maintenance

Regular Expression Support

Seven RegEx functions built into T-SQL, based on the RE2 library. Requires compat level 170.

-- REGEXP_LIKE: Test if pattern matches
SELECT * FROM dbo.Customers
WHERE REGEXP_LIKE(Email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$');

-- REGEXP_REPLACE: Replace matches
SELECT REGEXP_REPLACE(PhoneNumber, '[^0-9]', '') AS digits_only
FROM dbo.Contacts;

-- REGEXP_SUBSTR: Extract matching substring
SELECT REGEXP_SUBSTR(LogMessage, 'ERROR:\s*(.+)', 1, 1, '', 1) AS error_text
FROM dbo.Logs;

-- REGEXP_INSTR: Find position of match
SELECT REGEXP_INSTR(Description, '\d{3}-\d{2}-\d{4}') AS ssn_position
FROM dbo.Records;

-- REGEXP_COUNT: Count matches
SELECT REGEXP_COUNT(Content, '\b[A-Z][a-z]+\b') AS capitalized_word_count
FROM dbo.Articles;

Performance notes:

• RegEx functions are NOT SARGable -- they cannot use indexes for seeking
• Use RegEx for validation and extraction, not as primary filter predicates on large tables
• Filter with SARGable predicates first, then apply RegEx to the reduced result set
• RE2 is designed for linear-time matching (no catastrophic backtracking)

Native JSON Data Type and JSON Index

SQL Server 2025 adds a first-class JSON data type (up to 2 GB) and specialized JSON indexes:

-- Native JSON column
CREATE TABLE dbo.Events (
    EventID INT PRIMARY KEY,
    EventData JSON NOT NULL  -- native JSON type, validated on insert
);

-- Insert JSON data (validated automatically)
INSERT INTO dbo.Events VALUES (1, '{"type":"click","page":"/home","ts":"2025-01-15"}');

-- Create a JSON index
CREATE JSON INDEX IX_Events_Data ON dbo.Events(EventData);
-- Optimizes: JSON_VALUE, JSON_PATH_EXISTS, JSON_CONTAINS

-- New JSON functions
SELECT * FROM dbo.Events
WHERE JSON_CONTAINS(EventData, '$.tags', '"important"');

SELECT JSON_PATH_EXISTS(EventData, '$.metadata.author') AS has_author
FROM dbo.Events;

-- JSON_ARRAY and JSON_OBJECT constructors
SELECT JSON_OBJECT('id':EventID, 'data':EventData) AS wrapped
FROM dbo.Events;

Advantages over NVARCHAR JSON (pre-2025):

• Binary storage format (more compact, faster parsing)
• Validation on insert (malformed JSON rejected)
• JSON index for optimized query performance
• Up to 2 GB per document

Optimized Locking

Optimized locking reduces lock blocking and lock memory consumption. Based on two mechanisms:

1. Transaction ID (TID) locking -- Uses a single lock on the transaction ID to protect all modified rows, instead of individual row/key locks
2. Lock After Qualification (LAQ) -- Acquires locks only after evaluating query predicates, reducing unnecessary locking

-- Enable optimized locking (requires ADR)
ALTER DATABASE [MyDB] SET ACCELERATED_DATABASE_RECOVERY = ON;
ALTER DATABASE [MyDB] SET OPTIMIZED_LOCKING = ON;

-- Verify
SELECT name, is_optimized_locking_on
FROM sys.databases WHERE name = 'MyDB';

Benefits:

• Eliminates lock escalation concerns for most workloads
• Reduces LCK_M_* waits from concurrent DML
• Lower memory consumption for lock structures
• No application code changes required

Note: Optimized locking is disabled by default in SQL Server 2025. It requires Accelerated Database Recovery to be enabled first.

REST API Integration

Call external REST APIs directly from T-SQL:

-- Call an AI model (e.g., Azure OpenAI) from T-SQL
DECLARE @response NVARCHAR(MAX), @status INT;
EXEC sp_invoke_external_rest_endpoint
    @url = 'https://myopenai.openai.azure.com/openai/deployments/gpt-4/chat/completions?api-version=2024-02-15-preview',
    @method = 'POST',
    @headers = '{"api-key":"your-key"}',
    @payload = '{"messages":[{"role":"user","content":"Summarize this text: ..."}]}',
    @response = @response OUTPUT,
    @status = @status OUTPUT;

SELECT JSON_VALUE(@response, '$.choices[0].message.content') AS ai_response;

This enables AI-powered queries directly in the database layer without external application code.

Change Event Streaming

Real-time change data streaming from SQL Server without polling:

-- Enable change event streaming
ALTER DATABASE [MyDB] SET CHANGE_EVENT_STREAMING = ON;

-- Consumers receive change events as a stream
-- Integrates with Kafka, Azure Event Hubs, and custom consumers

Replaces the polling-based change tracking and change data capture patterns with push-based streaming.

Microsoft Fabric Mirroring

Near real-time replication of OLTP data to Microsoft Fabric for analytics:

• No ETL pipelines required
• Automatic delta synchronization
• Data lands in Fabric OneLake in open Delta Lake format
• Query mirrored data with Fabric SQL analytics endpoint

Security Defaults

SQL Server 2025 hardens security defaults:

• Encrypt=True by default for all connections (breaking change for legacy apps)
• TLS 1.3 enabled by default on new installations
• Microsoft Entra ID integration with managed identities for Azure service connections
• Granular UNMASK permissions -- Dynamic Data Masking can now be granted per column

-- Entra ID managed identity for outbound connections
CREATE DATABASE SCOPED CREDENTIAL AzureIdentity
WITH IDENTITY = 'Managed Identity';

Migration warning: The Encrypt=True default breaks legacy applications that use unencrypted connections. Update connection strings or set Encrypt=False explicitly during migration.

Standard Edition Improvements

SQL Server 2025 Standard Edition limits increased significantly:

• RAM: 256 GB (up from 128 GB)
• CPU cores: 32 (up from 24)
• Database size: Unlimited (was 524 GB -- removed in previous versions)

This reduces the need for Enterprise Edition in many workloads.

Additional 2025 Features

• SSRS replaced by Power BI Report Server (PBIRS) -- No new SSRS versions
• Intelligent Query Processing enhancements -- Continued improvements to PSP, DOP feedback, CE feedback
• ABORT_AFTER_WAIT improvements -- Better handling of online index operations blocked by queries
• Managed Instance link enhancements -- Improved hybrid connectivity with Azure SQL MI

Version Boundaries

• This agent covers SQL Server 2025 (compat level 170) -- the latest version
• All features from 2016-2022 are available (at their respective compat levels)
• Compat 170 is required for: RegEx functions, certain IQP enhancements
• Removed features:
  • Stretch Database (deprecated in 2022, removed in 2025)
  • Big Data Clusters (deprecated in 2019 CU28, removed)
  • SQL Server Reporting Services (replaced by Power BI Report Server)

Common Pitfalls

1. Encrypt=True breaking legacy apps -- Connection strings that do not specify Encrypt= previously defaulted to False. Now they default to True. Legacy apps without valid certificates will fail to connect. Fix: install proper TLS certificates or add Encrypt=False;TrustServerCertificate=True to connection strings (not recommended for production).
2. Vector index sizing -- DiskANN indexes can be large. A 1536-dimension vector is ~6 KB per row. Plan storage carefully for tables with millions of rows.
3. RegEx not SARGable -- Do not use REGEXP_LIKE as a primary filter on large tables without a supporting index-friendly predicate first.
4. Optimized locking requires ADR -- You must enable Accelerated Database Recovery before enabling optimized locking. ADR adds PVS overhead.
5. JSON type migration -- Changing existing NVARCHAR(MAX) JSON columns to the JSON type requires validation of all existing data. Invalid JSON will cause migration failures.
6. sp_invoke_external_rest_endpoint latency -- REST calls from T-SQL block the query. Do not use in hot-path OLTP queries. Best for batch processing or stored procedures.
7. Fabric mirroring network requirements -- Requires outbound connectivity to Azure. Not available in air-gapped environments.
8. SSRS to PBIRS migration -- Existing SSRS reports need migration to Power BI Report Server. Plan and test before upgrading.

Migration from SQL Server 2022

When upgrading from SQL Server 2022 (compat level 160) to 2025 (compat level 170):

1. Test connection strings -- The Encrypt=True default is the highest-impact breaking change. Audit all application connection strings.
2. Enable Query Store at compat 160 -- Capture baseline performance
3. Upgrade engine -- Keep compat level at 160 initially
4. Install TLS certificates -- Ensure valid certificates are installed for encrypted connections
5. Change compat level to 170 -- Unlocks RegEx functions, new IQP improvements
6. Evaluate new features:
   • Vector data type for semantic search / AI workloads
   • Native JSON type for document-oriented data
   • RegEx functions to replace CLR-based regex or complex LIKE patterns
   • Optimized locking for high-concurrency OLTP
   • REST API integration for AI enrichment
7. Remove deprecated features:
   • Migrate Stretch Database workloads before upgrade (removed in 2025)
   • Migrate SSRS reports to Power BI Report Server
8. Standard Edition evaluation -- Higher resource limits may eliminate the need for Enterprise Edition

Known Behavioral Changes at Compat 170

• REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_COUNT functions available
• Connection encryption enabled by default
• TLS 1.3 enabled by default
• Additional IQP improvements activate

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