New AI capabilities in SQL Server 2025

(Side note: Excited to share that the audio version of my book “Deciphering Data Architectures: Choosing Between a Modern Data Warehouse, Data Fabric, Data Lakehouse, and Data Mesh” is now accessible for those who prefer this format. The audio reader truly brings the content to life! Order your copy here or from most other online bookstores.)

I’ve had a number of customers asking about the new AI features in SQL Server 2025, so I wanted to write a quick post summarizing what’s available today. In reality, there are two major capabilities worth highlighting: Copilot in SQL Server Management Studio (SSMS) and support for vector data types with semantic search.

Keep in mind that SQL Server 2025 is still in preview, with Release Candidate (RC0) recently announced. You can find the full list of updates in the official documentation: What’s new in SQL Server 2025 Preview.

Copilot in SQL Server Management Studio (SSMS)

One of the most exciting new AI features in SQL Server 2025 is the introduction of Copilot in SSMS (requires SSMS 21). This capability allows you to interact with your data using natural language instead of writing T-SQL from scratch. With Copilot, DBAs and developers can quickly perform text and data analytics without needing to move data elsewhere.

Copilot in SSMS lets you:

Ask natural-language questions about your database and environment
Get help writing and optimizing T-SQL
Explore and analyze data without knowing SQL in detail

For example:
Type “List all orders from the last 30 days for New York customers” and Copilot will return both the T-SQL query:

SELECT * 
FROM Orders 
WHERE OrderDate > DATEADD(DAY, -30, GETDATE()) 
  AND City = 'New York';

and the corresponding results. This makes it easy to learn T-SQL, validate your intent, and accelerate development.

Privacy, Security, and Responsible AI

Copilot in SSMS is built on Azure OpenAI resources, which you provision in your own subscription. Importantly, it does not retain prompts, responses, or system metadata, and your data is never used to train or retrain AI models. It follows Microsoft’s Responsible AI practices for Azure OpenAI models, with more details available in the Transparency Note for Copilot in SQL Server Management Studio and Data, privacy, and security for Azure OpenAI Service.

Supported Databases and Permissions

Copilot works across:

SQL Server
Azure SQL Database
Azure SQL Managed Instance
SQL Database in Microsoft Fabric

It respects your database permissions. If your login doesn’t have access to a table, Copilot won’t be able to run queries against it. For example, if you don’t have permission to query Sales.Orders, a request to select from that table will fail.

Beyond Query Writing

Copilot isn’t just for writing queries—it’s also useful for database and environment exploration. You can ask questions like:

“What version of SQL Server is this instance running?”
“Which columns store email addresses?”
“How will changing compatibility mode affect query performance?”
“What queries have executed most frequently in the last two hours?”
“What’s the difference between a full and log backup?”

It can even help with database development tasks, such as creating tables, indexes, or generating sample data.

Getting Started

To use Copilot in SSMS, you’ll need (see instructions):

An Azure OpenAI endpoint and deployment in your subscription (see create the necessary Azure OpenAI resources). Note that the future SSMS 22 will use your Github Copilot account instead of an Azure OpenAI endpoint – see What’s next for Copilot in SSMS.
The latest version of SQL Server Management Studio with Copilot enabled.

Once configured, you’ll be able to start writing Language-Assisted Queries (LAQ)—natural language instructions that Copilot translates into valid T-SQL, returning both the code and results (like charts or tables).

More info on using Copilot in SSMS can be found here.

Vector Data Types with Semantic Search

The second major AI capability in SQL Server 2025 is support for vector data types and functions, enabling true semantic search directly inside the database. With this feature, you can store AI-generated embeddings as vectors, compare them using functions like VECTOR_SEARCH(), and generate embeddings inline with AI_GENERATE_EMBEDDINGS().

This unlocks use cases such as:

Find products similar to this one
Show me documents related to this query
Surface support tickets about login issues

Instead of matching just keywords, SQL Server can now search by meaning.

How Semantic Search Works

Text → Embeddings
Text (like descriptions, reviews, or documents) is converted into embeddings — high-dimensional numeric vectors created by an AI model such as Azure OpenAI.
Store in a VECTOR Column
SQL Server introduces a new VECTOR column type to persist embeddings.
Similarity Search with VECTOR_SEARCH()
Queries compare embeddings based on semantic closeness, not just exact words.

Example: Finding Similar Products

-- Step 1: Create OpenAI model
CREATE EXTERNAL MODEL MyOpenAI_Embedding_Model
WITH (
      LOCATION = 'https://my-azure-openai-endpoint.openai.azure.com/openai/deployments/text-embedding-ada-002/embeddings?api-version=2023-05-15',
      API_FORMAT = 'Azure OpenAI',
      MODEL_TYPE = EMBEDDINGS,
      MODEL = 'text-embedding-ada-002'
);

-- Step 2: Create table with a VECTOR column
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Description NVARCHAR(MAX),
    DescriptionEmbedding VECTOR(1536)  -- new in SQL Server 2025, stores AI-generated vector
);

-- Step 3: Create vector index (DiskANN)
CREATE VECTOR INDEX vec_idx_Products_DescriptionEmbedding
ON Products (DescriptionEmbedding)
WITH (
    METRIC = 'cosine',      -- can also use 'dot' or 'euclidean'
    TYPE = 'diskann',       -- algorithm type (only DiskANN supported in preview)
    MAXDOP = 4              -- optional, controls degree of parallelism
)
ON [PRIMARY];               -- or your preferred filegroup

-- Step 4: Insert data with embeddings
INSERT INTO Products (ProductID, Name, Description, DescriptionEmbedding)
VALUES (
    1,
    'Wireless Earbuds',
    'Bluetooth earbuds with noise cancellation and 24-hour battery life.',
    AI_GENERATE_EMBEDDINGS('Bluetooth earbuds with noise cancellation and 24-hour battery life.' USE MODEL = MyOpenAI_Embedding_Model)
);

-- Step 5: Run a semantic search
DECLARE @queryVector VECTOR(1536) = 
    AI_GENERATE_EMBEDDINGS('Noise-cancelling wireless headphones' USE MODEL = MyOpenAI_Embedding_Model);

SELECT TOP 5 p.ProductID, p.Name, p.Description, v.distance AS Similarity
FROM VECTOR_SEARCH(
    TABLE = Products AS p,
    COLUMN = DescriptionEmbedding,
    SIMILAR_TO = @queryVector,
    TOP_N = 5,
    METRIC = 'cosine'
) AS v
ORDER BY v.distance;

This returns the top 5 products whose descriptions are semantically closest to “noise-cancelling wireless headphones” — even if those exact words never appear.

Why It Matters

Traditional Full-Text Search (FTS) matches words, stems, or phrases. That fails when:

Synonyms are used (earbuds vs. headphones)
Users type casual language (can’t log in vs. authentication error)
Context or intent matters more than literal words

With vector search, SQL Server can now match based on meaning.

Example:

DECLARE @queryVector VECTOR(1536) = 
    AI_GENERATE_EMBEDDINGS('I can’t log into the portal');

SELECT TOP 5 TicketID, Subject, Body, Distance
FROM VECTOR_SEARCH(
    TABLE = SupportTickets,
    COLUMN = DescriptionEmbedding,
    SIMILAR_TO = @queryVector,
    TOP_N = 5,
    METRIC = 'cosine'
ORDER BY Distance;

This surfaces tickets about login problems — even if the phrase “log into the portal” never appears.

SQL Server as a Vector Database

SQL Server 2025 brings capabilities typically found in specialized vector databases like Pinecone, Weaviate, or Milvus, — as well as in search services like Azure Cognitive Search, but with the advantage of being integrated into the same platform enterprises already use for structured data.

That means you can:

Avoid managing a separate vector database
Add semantic search to catalogs, support tickets, or documents
Build Retrieval-Augmented Generation (RAG) systems with T-SQL and Azure OpenAI

And since Copilot in SSMS works with vector search, you can simply type “Show me electronics that block outside sound” and SQL Server will translate that into a semantic search using vector comparison.

The bottom line: SQL Server 2025 turns your database into a vector-powered AI engine, enabling smarter, more relevant search and analytics without leaving T-SQL.

SQL Server 2025’s vector-powered AI engine can certainly enhance a simple SELECT statement by making it return more relevant text results. But its real value shines as part of a larger Generative AI (GenAI) architecture, where the database retrieves contextually relevant text for a user’s question and sends both to a large language model (LLM). This approach, known as Retrieval-Augmented Generation (RAG), improves accuracy by grounding the LLM’s response in your own data. I covered this pattern in more detail in my post Introduction to OpenAI and LLMs.

The post New AI capabilities in SQL Server 2025 first appeared on James Serra's Blog.