Your users don’t search the way your LIKE clause does

LIKE ‘%noise%’ was never going to find quiet headphones

Ginger Grant and I recently presented Developing a grounded AI model with SQL Server 2025 virtually for a user group. This post covers the ground we went over, for anyone who couldn’t make it and for those who were there and want the details without scribbling notes off a slide.

The short version: SQL Server 2025 gives you the building blocks to put AI-driven search over your own data, using T-SQL you already know, with models you choose and data that doesn’t have to leave your server. The long version is below.

THE PROBLEM WE’VE ALWAYS HAD

Say you’re after a pair of noise-cancelling headphones for a long flight. You type “noise” into a search box, and the application runs something like this:

SELECT ProductName, Description
FROM dbo.Products
WHERE Description LIKE N'%noise%';

That finds every product with the literal word “noise” in the description. It doesn’t find “Quiet plane headphones”, which to any human reading it means exactly the same thing. It doesn’t find “peaceful”, “hushed” or “silent”. And if the vendor wrote the description in French or German, it finds nothing at all.

We’ve spent years working around this. Full-text search with CONTAINS and CONTAINSTABLE helps with word forms and proximity, but it’s still matching words, not meaning. The alternative is building ever-larger OR clauses to cover every synonym somebody might type, and that gets complex and slow quickly.

Users don’t search the way a LIKE clause does. They describe what they want. That gap is the problem AI search is trying to close.

VECTORS:  A FINGERPRINT OF MEANING

A vector is a list of numbers that acts as a fingerprint of the meaning of a piece of text (or an image, or audio). Two pieces of text that mean similar things end up with similar-looking lists of numbers, even when they don’t share a single word.

Before SQL Server 2025, people stored these as VARBINARY(MAX) or as JSON strings in NVARCHAR(MAX) and parsed them on every query. That cost CPU on every read, couldn’t be indexed, and nothing stopped you storing a 384-number vector in a column meant for 768.

SQL Server 2025 adds a native VECTOR data type. It’s generally available, and:

• Each element is a single-precision, 4-byte float by default. Half-precision (float16) is available as a preview feature.
• The dimension count is enforced by the column definition, up to 1,998 dimensions.
• It’s stored in an optimised binary format and exposed as a JSON array when you look at it.

ALTER TABLE dbo.Listings
ADD Embedding VECTOR(384) NULL;

The dimension count isn’t something you pick. It comes from the embedding model you use.

EMBEDDINGS: HOW TEXT BECOMES A VECTOR

An embedding is the process of turning text like “a quiet home” into its vector. You don’t train anything. A pretrained model that already understands language does the work, and SQL Server calls it for you with AI_GENERATE_EMBEDDINGS().

What’s worth being clear about is that SQL Server isn’t doing the maths itself. AI_GENERATE_EMBEDDINGS() sends a REST request to the model you’ve defined in the database. Where that model lives is your decision:

• Local models, such as Ollama running on your own hardware. Nothing leaves your environment.
• Azure OpenAI or OpenAI. Managed and hosted, but your data and your users’ questions are going out to an external service.
• Microsoft Foundry, where you bring a model and Microsoft hosts it.
• ONNX Runtime, running a model in-process on the SQL Server host. This one is still a preview feature in SQL Server 2025.

For a lot of organisations, “can this data leave the building?” decides the question before anything technical does. For the session we went with local models.

The other thing to know up front: different models produce different vectors for the same text. You’ll likely want to try a couple to see which gives the best results for your data, and whichever model you use to embed your data has to be the same one you use to embed the search question. Mix them and the distances mean nothing.

GETTING SQL SERVER TALKING TO A LOCAL MODEL

Our demo stack was Ollama serving the models, with Caddy in front of it. Here’s why both are needed.

Ollama serves models over a REST API on port 11434, over plain HTTP. SQL Server will only call external model endpoints over HTTPS with TLS. So something has to sit in front of Ollama and handle HTTPS. We used Caddy, a web server with a built-in reverse proxy that doesn’t need installing. It’s a single executable and a small text config file:

localhost:11435 {
    reverse_proxy localhost:11434
}

Caddy issues a certificate for localhost from its own local certificate authority. SQL Server has to trust that certificate, so make sure Caddy’s root certificate is in the machine’s trusted store. Microsoft’s own getting-started walkthrough does the same job with nginx, so use whichever you’re more comfortable with.

With Ollama running, pull the models you want to test:

ollama pull all-minilm
ollama pull nomic-embed-text

all-minilm produces 384-dimension vectors and nomic-embed-text produces 768, which is a good example of why the model decides your column definition.

On the SQL Server side, set the database to the SQL Server 2025 compatibility level, enable preview features (you’ll need them for vector indexes), and switch on outbound REST calls, which are disabled by default:

ALTER DATABASE AdventureWorksDW2020
SET COMPATIBILITY_LEVEL = 170;
ALTER DATABASE SCOPED CONFIGURATION
SET PREVIEW_FEATURES = ON;
EXECUTE sp_configure 'external rest endpoint enabled', 1;
RECONFIGURE WITH OVERRIDE;

Then define the model and generate the embeddings:

CREATE EXTERNAL MODEL ProductEmbeddingModel
AUTHORIZATION AI_ModelOwner
WITH (
    LOCATION   = 'https://localhost:11435/api/embed',
    API_FORMAT = 'Ollama',
    MODEL_TYPE = EMBEDDINGS,
    MODEL      = 'all-minilm'
);
UPDATE dbo.Listings
SET Embedding = AI_GENERATE_EMBEDDINGS(Description USE MODEL ProductEmbeddingModel);

Turning on outbound REST calls from your database engine is a security decision, not just a setup step. Do it deliberately and control who can use it.

DISTANCE IS THE NEW WHERE CLAUSE

Once your data and your question are both vectors, searching becomes measuring how far apart they are. VECTOR_DISTANCE() does that, with three metrics:

• Cosine is the default and most common. It cares about the direction of the vectors, not their length, which suits text embeddings. Values run from 0 (identical meaning) to 2 (opposite). In practice most text embeddings land somewhere between 0 and 1.
• Euclidean measures straight-line distance. It’s useful for image or numeric feature embeddings where magnitude matters.
• Dot is the negative dot product, and takes both direction and magnitude into account.

So “quiet home” against “peaceful retreat” comes out with a small distance, and “quiet home” against “busy motorway frontage” comes out large. You’re no longer matching words. You’re ranking by closeness of meaning.

DECLARE @qv VECTOR(384) = AI_GENERATE_EMBEDDINGS(
   N'quiet home away from busy roads' USE MODEL ProductEmbeddingModel);
SELECT TOP (10) Address, Price,
       VECTOR_DISTANCE('cosine', Embedding, @qv) AS Distance
FROM dbo.Listings
ORDER BY Distance;

That’s an exact k-nearest-neighbour (kNN) search. It compares the question against every row. VECTOR_DISTANCE() never uses a vector index, even if one exists, and it’s fine for a few thousand rows. For a few million it gets painful.

SCALING UP: DISKANN INDEXES

For scale, SQL Server 2025 adds vector indexes built on DiskANN, an approximate nearest neighbour (ANN) algorithm. Rather than checking every row, it jumps to the likely neighbourhood using a prebuilt graph, and as the name says, the index lives on disk rather than in memory. You give up a small amount of accuracy for a very large gain in speed. Most of the time you get the same top results as a full scan.

CREATE VECTOR INDEX vix_Listings_Embedding
ON dbo.Listings (Embedding)
WITH (METRIC = 'cosine', TYPE = 'DiskANN');

The index only gets used when you query through VECTOR_SEARCH(). On SQL Server 2025 today, that looks like this:

SELECT t.Address, t.Price, s.distance
FROM VECTOR_SEARCH(
        TABLE      = dbo.Listings AS t,
        COLUMN     = Embedding,
        SIMILAR_TO = @qv,
        METRIC     = 'cosine',
        TOP_N      = 10
     ) AS s
ORDER BY s.distance;

Before you rely on it, there are a few things to know:

• It’s a preview feature in SQL Server 2025. CREATE VECTOR INDEX and VECTOR_SEARCH both need PREVIEW_FEATURES on, and the syntax is still changing.
• You need at least 100 rows with non-null vectors before the index can be built. Below that you get error 42266. That’s why our demo padded the Listings table out to 150 rows.
• The newer index version isn’t in the box product yet. Microsoft has released a newer vector index version that supports full INSERT/UPDATE/DELETE, filters inside the search, and ANSI-style `SELECT TOP (N) WITH APPROXIMATE` syntax in place of TOP_N. Microsoft Learn currently lists it as available only in Azure SQL Database and SQL database in Microsoft Fabric. On SQL Server 2025, treat a vector-indexed table as read-mostly and check the documentation against your build before you design around full DML.

PUTTING IT TOGETHER: RETRIEVAL AUGMENTED GENERATION

On their own, vectors give you better search. The next step is Retrieval Augmented Generation (RAG), which is where the “grounded” in the session title comes from.
The flow goes like this:

1. Your data is embedded and stored alongside the rows it describes.
2. A user or an AI agent asks a question in plain language, for example: “What house listings are in a quiet neighbourhood and under $700k?”
3. That question is embedded with the same model, and VECTOR_SEARCH finds the closest matching rows. You still use an ordinary WHERE clause for the price, because structured queries are still what a relational database does best.
4. The question and those rows go to a language model, which writes a conversational answer grounded in your actual data, not in whatever the model happens to remember from its training.

So instead of “here are three rows”, the user gets something like: “Based on your budget and preference for quiet streets, I’d look at these two, because…”. The model is working from your data, which is what makes the answer trustworthy enough to put in front of a customer.
That’s the answer to the LIKE ‘%noise%’ problem we started with.

SQL SERVER PLAYS BOTH SIDES

It’s worth pointing out that SQL Server now sits on both sides of the conversation. It’s the outbound caller, using AI_GENERATE_EMBEDDINGS or sp_invoke_external_rest_endpoint to reach models. And it’s the backend that AI applications and agents consume, whether through LangChain, Semantic Kernel, EF Core or Microsoft’s Agent Framework.

For agents specifically, Microsoft has released SQL MCP Server, an implementation of the Model Context Protocol (the open standard Anthropic published in late 2024 and since adopted by OpenAI, Google and AWS). The way I think about it: if you want an AI agent working with the data in your database, you need a framework for connectivity and security that’s safe and sensible. That’s what MCP provides.

The important part is that SQL MCP Server is built into Data API Builder. It doesn’t let an agent write and run whatever T-SQL it likes. It exposes a fixed set of typed operations (describe, read, create, update, delete, execute, aggregate) over entities you’ve defined, and it inherits Data API Builder’s role-based access control, caching and telemetry. The same entity definitions drive REST, GraphQL and MCP endpoints.

On the tooling side, GitHub Copilot is now generally available in SSMS 22 and in VS Code with the MSSQL extension, which helps when you’re writing all of this.

SECURITY STAYS WHERE IT WAS

The part I’d want any DBA to take away is that none of this sits outside the security model you already run:

• You control access with SQL Server security, exactly as you do today.
• You choose which models are used and where they run, locally or in the cloud, isolated from the engine.
• Row-Level Security, TDE and Dynamic Data Masking still apply to the data being searched and returned.
• SQL Server Auditing can track all of it.
• Ledger tables are a good fit for tamper-evident chat history and feedback.

The data doesn’t move somewhere new to become AI-ready. The AI comes to the data, through controls you already understand.

WHERE TO START

If you want to try this, my suggestion is the path we took in the demo. Set up a local model with Ollama, put it behind HTTPS, pick one table with a decent text column, embed it, and compare what VECTOR_DISTANCE returns with what your current LIKE or full-text search returns. You’ll see the difference quickly.

Just keep the preview features in the lab until they’re GA, and treat the model choice as seriously as any other dependency in production.

—
Warwick Rudd is a Microsoft Certified Master and Data Platform MVP, and the founder of SQL Masters Consulting. If you’d like to talk about what AI-ready SQL Server looks like for your environment, get in touch.

FURTHER READING

• Vector data type
• Half-precision (float16) vector data type
• VECTOR_DISTANCE
• VECTOR_SEARCH
• CREATE VECTOR INDEX
• Vector search and vector indexes overview
• AI_GENERATE_EMBEDDINGS
• CREATE EXTERNAL MODEL
• sp_invoke_external_rest_endpoint
• SQL Server 2025 Vector and AI features released in RTM
• Getting started with AI in SQL Server 2025 on Windows
• SQL MCP Server overview
• Introducing SQL MCP Server
• GitHub Copilot in SSMS
• Ollama API documentation]
• Ollama FAQ
• Vector search in SQL Server 2025: what to watch out for