SQL Server 2025 Fundamentals: A Practical Guide from Basics to Advanced

SQL Server 2025 is a genuine step change — not a maintenance release. While it preserves everything DBAs and developers rely on, it adds features that would have seemed far-fetched in earlier versions: native vector search, AI model integration directly in T-SQL, built-in regular expressions, a native JSON data type, and a locking overhaul that can improve concurrency with no code changes required.

This article focuses on fundamentals — not just what SQL Server 2025 is, but how to actually use it. We cover the core concepts every SQL professional needs, explain what is genuinely new to 2025, and include a hands-on workshop that scales from novice to advanced.

What SQL Server 2025 Fundamentals Means

At its core, SQL Server 2025 is a relational database management system designed to store, retrieve, secure, and analyze structured data. Fundamentals are the concepts every SQL professional should understand before moving into advanced architecture or tuning:

• Database and file architecture
• Tables, indexes, and constraints
• Query execution and optimization
• Security and permissions
• Backup, recovery, and reliability
• The new 2025-specific features that extend all of the above

Understanding these well is what separates someone who can write queries from someone who can run production systems. SQL Server 2025 is powerful, but it rewards good fundamentals and punishes shortcuts — exactly as every version before it did.

What Is Actually New in SQL Server 2025

The original article described SQL Server 2025 as having “improved query intelligence and better hybrid scenarios” — which is accurate but significantly understates what actually shipped. Here is what is genuinely new:

Database and File Architecture

Every SQL Server database consists of data files and transaction log files. This separation is foundational to performance and recovery — and unchanged in SQL Server 2025.

CREATE DATABASE SalesDB
ON PRIMARY
(
    NAME       = SalesDB_Data,
    FILENAME   = 'C:\SQLData\SalesDB_Data.mdf',
    SIZE       = 1GB,
    FILEGROWTH = 256MB
)
LOG ON
(
    NAME       = SalesDB_Log,
    FILENAME   = 'C:\SQLLogs\SalesDB_Log.ldf',
    SIZE       = 512MB,
    FILEGROWTH = 128MB
);

Tables, Data Types, and Constraints

SQL Server 2025 expands the type system significantly with native JSON and VECTOR types, but the relational fundamentals are unchanged. Constraints protect data integrity and give the optimizer execution plan guarantees.

-- Standard table with constraints and defaults
CREATE TABLE dbo.Customers
(
    CustomerID  INT            IDENTITY(1,1) PRIMARY KEY,
    FirstName   NVARCHAR(100)  NOT NULL,
    LastName    NVARCHAR(100)  NOT NULL,
    Email       NVARCHAR(255)  UNIQUE,
    CreatedDate DATETIME2      NOT NULL DEFAULT SYSDATETIME()
);

-- SQL Server 2025: native JSON column alongside relational columns
ALTER TABLE dbo.Customers
ADD Preferences JSON NULL;

Indexes and Performance Basics

Indexes are the single most impactful performance feature in SQL Server. The rule is unchanged: indexes speed up reads but slow down writes. Every index must earn its place.

-- Standard nonclustered index
CREATE NONCLUSTERED INDEX IX_Customers_Email
ON dbo.Customers (Email);

-- Composite index: equality predicates first, range second, covering includes
CREATE NONCLUSTERED INDEX IX_Orders_Status_Date
ON dbo.Orders (Status, OrderDate)
INCLUDE (CustomerID, TotalAmount);

-- SQL Server 2025: DiskANN vector index for semantic similarity search
-- Requires PREVIEW_FEATURES = ON
CREATE VECTOR INDEX IX_Articles_Embedding
ON dbo.Articles (Embedding)
WITH (METRIC = 'cosine', TYPE = 'diskann');

Optimized Locking: The Concurrency Game Changer New in 2025

This is one of the most significant engine improvements in SQL Server 2025 for production OLTP systems. It uses two mechanisms to reduce blocking in concurrent write workloads — with no application code changes required.

-- Enable Optimized Locking on a database (SQL Server 2025)
ALTER DATABASE [YourDatabase] SET OPTIMIZED_LOCKING = ON;

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

Vector Search and AI Fundamentals New in 2025

SQL Server 2025 turns the database engine into a native vector store, enabling semantic search — finding rows similar in meaning rather than exact keyword matches — without a separate vector database service.

A vector embedding is a list of floating-point numbers generated by an AI model that represents the meaning of text, images, or other data. Similar concepts produce numerically close vectors. SQL Server 2025 stores these in a VECTOR column and indexes them with DiskANN for fast similarity queries.

-- Enable preview features (required for vector search)
ALTER DATABASE [YourDatabase] SET PREVIEW_FEATURES = ON;
GO

-- Table with a VECTOR column (1536 dimensions = OpenAI text-embedding-3-small)
CREATE TABLE dbo.Articles
(
    ArticleID INT           IDENTITY(1,1) PRIMARY KEY,
    Title     NVARCHAR(200) NOT NULL,
    Content   NVARCHAR(MAX) NULL,
    Embedding VECTOR(1536)  NULL
);

-- Define an AI model for embedding generation (Azure OpenAI example)
CREATE EXTERNAL MODEL MyEmbeddingModel
WITH
(
    LOCATION   = 'https://your-resource.openai.azure.com/',
    API_FORMAT = 'Azure OpenAI',
    MODEL_NAME = 'text-embedding-3-small',
    CREDENTIAL = MyAzureOpenAICredential
);

-- Generate and store embeddings in T-SQL
UPDATE dbo.Articles
SET Embedding = AI_GENERATE_EMBEDDINGS(Content USE MODEL MyEmbeddingModel)
WHERE Embedding IS NULL;

-- Create DiskANN vector index
CREATE VECTOR INDEX IX_Articles_Embedding
ON dbo.Articles (Embedding)
WITH (METRIC = 'cosine', TYPE = 'diskann');

-- Semantic similarity query: find articles similar to a search phrase
DECLARE @Query VECTOR(1536) =
    AI_GENERATE_EMBEDDINGS(N'How neural networks work' USE MODEL MyEmbeddingModel);

SELECT TOP 5
    ArticleID, Title,
    VECTOR_DISTANCE('cosine', Embedding, @Query) AS similarity_score
FROM dbo.Articles
ORDER BY similarity_score ASC;  -- Lower cosine distance = more similar

Native JSON and RegEx New in 2025

Native JSON Type

-- Native JSON column -- dedicated storage type, not NVARCHAR
CREATE TABLE dbo.UserProfiles
(
    UserID      INT  PRIMARY KEY,
    ProfileData JSON NOT NULL
);

-- Query JSON data
SELECT
    UserID,
    JSON_VALUE(ProfileData, '$.theme')       AS theme,
    JSON_QUERY(ProfileData, '$.permissions') AS permissions
FROM dbo.UserProfiles
WHERE JSON_VALUE(ProfileData, '$.role') = 'admin';

Built-in Regular Expressions

-- REGEXP_LIKE: test string against a regex pattern
SELECT CustomerID, Email
FROM dbo.Customers
WHERE REGEXP_LIKE(Email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$') = 1;

-- REGEXP_SUBSTR: extract a matched substring
SELECT OrderID, REGEXP_SUBSTR(ReferenceCode, '[A-Z]{2}\d{6}') AS extracted_code
FROM dbo.Orders;

Query Execution and Optimization

When you run a query, SQL Server generates an execution plan. SQL Server 2025 continues the Intelligent Query Processing improvements from 2022 and adds several new mechanisms:

• Cardinality Estimation (CE) feedback for expressions — the optimizer learns from past executions and adjusts estimates for complex expressions
• Optional Parameter Plan Optimization (OPPO) — runtime-based plan selection to address parameter sniffing issues
• Query Store on readable secondaries — captures workload on AG secondaries, not just the primary
• Optimized sp_executesql — reduces compilation storms from large dynamic SQL workloads

-- A query whose performance depends entirely on having the right index
SELECT CustomerID, FirstName, LastName
FROM dbo.Customers
WHERE Email = 'user@example.com';

-- If this is slow: check the execution plan
-- Seek = index used correctly
-- Scan = full table scan -- add or fix an index before scaling hardware

Security Fundamentals

SQL Server 2025 enforces TDS 8.0 and TLS 1.3 by default for all connections. PBKDF2 replaces older password hashing. Microsoft Entra managed identity is fully supported. The principle of least privilege remains as important as ever.

-- Minimal-privilege application login
CREATE LOGIN AppUser
WITH PASSWORD       = 'StrongPassword!123',
     CHECK_POLICY   = ON,
     CHECK_EXPIRATION = ON;

CREATE USER AppUser FOR LOGIN AppUser;
GRANT SELECT, INSERT ON dbo.Orders TO AppUser;

-- Verify effective permissions
EXECUTE AS USER = 'AppUser';
SELECT HAS_PERMS_BY_NAME('dbo.Orders', 'OBJECT', 'SELECT') AS can_select;
SELECT HAS_PERMS_BY_NAME('dbo.Orders', 'OBJECT', 'DELETE') AS can_delete; -- should be 0
REVERT;

Backup and Recovery

SQL Server 2025 adds ZSTD compression — faster and smaller than previous compression — and the ability to run full and differential backups on secondary AG replicas to reduce primary workload.

-- Full backup with ZSTD compression (SQL Server 2025)
BACKUP DATABASE [SalesDB]
TO DISK = N'\\backup-share\SalesDB_Full.bak'
WITH COMPRESSION, ALGORITHM = ZSTD, CHECKSUM, STATS = 5;

-- Backup on a secondary AG replica -- offloads I/O from the primary
BACKUP DATABASE [SalesDB]
TO DISK = N'\\backup-share\SalesDB_Full_Secondary.bak'
WITH COMPRESSION, CHECKSUM, COPY_ONLY, STATS = 5;

Pros and Cons of SQL Server 2025

Hands-On Workshop: Novice to Advanced

Build a small but representative database while working through core SQL Server 2025 fundamentals. Each step introduces concepts that build on the previous one.

CREATE DATABASE WorkshopDB;
GO
USE WorkshopDB;
GO

CREATE TABLE dbo.Orders
(
    OrderID      INT           IDENTITY(1,1) PRIMARY KEY,
    CustomerName NVARCHAR(200) NOT NULL,
    OrderTotal   DECIMAL(10,2) NOT NULL,
    OrderDate    DATETIME2     NOT NULL DEFAULT SYSDATETIME(),
    Notes        JSON          NULL       -- native JSON column
);

INSERT INTO dbo.Orders (CustomerName, OrderTotal, Notes)
VALUES
    ('Alice',   120.50, '{"priority":"high",  "source":"web"}'),
    ('Bob',      75.00, '{"priority":"normal","source":"app"}'),
    ('Charlie', 300.00, '{"priority":"high",  "source":"phone"}');

-- Standard query
SELECT * FROM dbo.Orders ORDER BY OrderDate DESC;

-- Query JSON data natively
SELECT
    CustomerName, OrderTotal,
    JSON_VALUE(Notes, '$.priority') AS priority,
    JSON_VALUE(Notes, '$.source')   AS source
FROM dbo.Orders
WHERE JSON_VALUE(Notes, '$.priority') = 'high';

CREATE NONCLUSTERED INDEX IX_Orders_OrderDate
ON dbo.Orders (OrderDate)
INCLUDE (CustomerName, OrderTotal);

-- Run a filtered query and look for Index Seek in the execution plan
SELECT CustomerName, OrderTotal
FROM dbo.Orders
WHERE OrderDate >= DATEADD(DAY, -30, SYSDATETIME());

-- Enable Optimized Locking (no code changes required)
ALTER DATABASE WorkshopDB SET OPTIMIZED_LOCKING = ON;

-- Enable Query Store in Read-Write mode
ALTER DATABASE WorkshopDB SET QUERY_STORE = ON;
ALTER DATABASE WorkshopDB SET QUERY_STORE
(
    OPERATION_MODE      = READ_WRITE,
    CLEANUP_POLICY      = (STALE_QUERY_THRESHOLD_DAYS = 30),
    MAX_STORAGE_SIZE_MB = 1024,
    QUERY_CAPTURE_MODE  = AUTO
);

-- Verify both settings
SELECT name, is_optimized_locking_on, is_query_store_on
FROM sys.databases WHERE name = 'WorkshopDB';

CREATE LOGIN AppUser
WITH PASSWORD       = 'StrongPassword!123',
     CHECK_POLICY   = ON,
     CHECK_EXPIRATION = ON;

CREATE USER AppUser FOR LOGIN AppUser;
GRANT SELECT, INSERT ON dbo.Orders TO AppUser;

-- Verify effective permissions
EXECUTE AS USER = 'AppUser';
SELECT HAS_PERMS_BY_NAME('dbo.Orders', 'OBJECT', 'SELECT') AS can_select;
SELECT HAS_PERMS_BY_NAME('dbo.Orders', 'OBJECT', 'DELETE') AS can_delete;
REVERT;

-- Top 10 queries by total CPU consumption
SELECT TOP 10
    qsqt.query_sql_text,
    SUM(qsrs.avg_cpu_time * qsrs.count_executions) AS total_cpu_us,
    SUM(qsrs.count_executions)                     AS total_executions,
    AVG(qsrs.avg_duration)                         AS avg_duration_us,
    MAX(qsp.last_execution_time)                   AS last_seen
FROM sys.query_store_query          qsq
JOIN sys.query_store_query_text     qsqt ON qsqt.query_text_id = qsq.query_text_id
JOIN sys.query_store_plan           qsp  ON qsp.query_id       = qsq.query_id
JOIN sys.query_store_runtime_stats  qsrs ON qsrs.plan_id       = qsp.plan_id
GROUP BY qsqt.query_sql_text
ORDER BY total_cpu_us DESC;

Final Thoughts

SQL Server 2025 does not require you to relearn SQL. But it rewards those who master both the timeless fundamentals and the genuinely new capabilities this version brings. Optimized Locking can improve concurrency in systems that have struggled with blocking for years — with no code changes. Native vector search removes an entire category of external dependency for teams building AI-integrated applications. Native JSON and RegEx bring T-SQL closer to the expressiveness of modern application languages.

References

• Microsoft Docs – What’s New in SQL Server 2025
• Microsoft Docs – Vector Data Type in SQL Server 2025
• Microsoft Docs – Optimized Locking
• Microsoft Docs – JSON Data in SQL Server
• Microsoft Docs – Query Store Overview
• Microsoft Docs – Security Best Practices
• Tim Radney – SQL Server 2025: What’s New and Why It Matters
• Microsoft Blog – Announcing SQL Server 2025