{"id":111121,"date":"2026-06-17T12:00:00","date_gmt":"2026-06-17T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=111121"},"modified":"2026-06-17T12:29:27","modified_gmt":"2026-06-17T12:29:27","slug":"vector-search-in-sql-server-vector_distance-vector_search-and-index-trade-offs","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/vector-search-in-sql-server-vector_distance-vector_search-and-index-trade-offs\/","title":{"rendered":"Vector search in SQL Server: VECTOR_DISTANCE, VECTOR_SEARCH, and index trade-offs"},"content":{"rendered":"\n

In this article, I’ll explain how to implement vector search in SQL Server using the VECTOR_DISTANCE<\/code> and VECTOR_SEARCH<\/code> functions. You’ll learn how vector indexes work, when to use them, and what the key trade-offs are \u2014 including the current read-only table limitation. I’ll also cover the latest syntax changes in Azure SQL Database, where that limitation has already been lifted.<\/strong><\/p>\n\n\n\n

This is the third article in Greg Low\u2019s series \u2018AI text embeddings in SQL Server: everything you need to know\u2019.<\/a><\/em><\/strong><\/p>\n\n\n\n

The VECTOR_DISTANCE function in SQL Server<\/h2>\n\n\n\n

Like how text embeddings<\/a> are a representation of a location in a number space, any text that is similar in meaning to one another is located close together.<\/p>\n\n\n\n

For example, I have a table of products with many columns but, for our purposes, the key ones are ProductID<\/code> as an integer primary key<\/a>, and a ProductName<\/code> that’s a varchar<\/a>(50) column. Here are the first 10 rows of the table:<\/p>\n\n\n\n

\"The<\/figure>\n\n\n\n

I’ll start by adding a column to hold the vector embeddings:<\/p>\n\n\n\n

ALTER TABLE dbo.Products\nADD ProductNameEmbeddings vector(384);\nGO<\/pre><\/div>\n\n\n\n
Note that I’ve used a vector column with 384 dimensions. This is because I’m going to use the AllMiniLM external model that I created in the last article, and it has 384 dimensions. <\/p>\n\n\n\n
Next, I’ll make sure that both Ollama<\/a> and Caddy<\/a> are running, and then I’ll populate this new column by calling the external model:<\/p>\n\n\n\n
UPDATE dbo.Products\nSET ProductNameEmbeddings = AI_GENERATE_EMBEDDINGS(ProductName USE MODEL AllMiniLM);\nGO<\/pre><\/div>\n\n\n\n
Once I have the embeddings in place, I can use the VECTOR_DISTANCE<\/code><\/a> function to check similarity:<\/p>\n\n\n\n
DECLARE @UserEmbeddings vector(384) \n  = AI_GENERATE_EMBEDDINGS('Natural tomatoes from Italy' USE MODEL AllMiniLM);\n\nSELECT TOP(5) p.ProductID, p.ProductName \nFROM dbo.Products AS p\nORDER BY VECTOR_DISTANCE('cosine', @UserEmbeddings, p.ProductNameEmbeddings);<\/pre><\/div>\n\n\n\n
I started by taking a user prompt – Natural tomatoes from Italy<\/strong> – and calculated embeddings for it. I then used the VECTOR_DISTANCE()<\/code> function to compare that value to the embeddings for every row in the table<\/strong> based on cosine distance<\/a>. <\/p>\n\n\n\n
Note that I could also have used a Euclidean <\/a>or dot product based metric, but cosine is perfect for what I’m doing here. <\/p>\n\n\n\n
The values returned on my system were:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
That’s a great response for this query, and note how hard it would have been to get anywhere near that with other functions such as LIKE()<\/code>.<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Fast, reliable and consistent SQL Server development…<\/h3>\n                
\n                                            …with SQL Toolbelt Essentials. 10 ingeniously simple tools for accelerating development, reducing risk, and standardizing workflows.                                    <\/div>\n            <\/div>\n                                            Learn more & try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
Vector indexes in SQL Server<\/h2>\n\n\n\n
While the query I had above is fine for a small number of rows, it would simply be far too compute-intensive with a large number of rows. There are two workarounds for this.<\/p>\n\n\n\n
The first is to filter the rows some other way, like just using a standard WHERE<\/a> clause. That should always be your first approach.<\/p>\n\n\n\n
The second approach is to create a vector index<\/a>, for example:<\/p>\n\n\n\n
CREATE VECTOR INDEX VX_dbo_Products\nON dbo.Products (ProductNameEmbeddings)\nWITH (\n    metric = 'cosine',\n    type   = 'diskann',\n    maxdop = 4\n);\nGO<\/pre><\/div>\n\n\n\n
Creating a vector index is similar to creating other SQL Server indexes, but with the advantage of having a few more options than the others. The main one is the metric. <\/p>\n\n\n\n
Since the index is storing a result of a calculation, it needs to know – at creation time – which distance metric<\/strong> will be used. The type<\/strong> shown here is diskann<\/code> (or disk approximate nearest neighbor). You can also specify the maxdop<\/code> <\/strong>(maximum degrees of parallelism) for the index operation.<\/p>\n\n\n\n
The name diskann<\/code> should  hint at something different here. Vector indexes are used to find approximate<\/em> outcomes – they don’t guarantee precise<\/em> outcomes. You must keep this in mind when using them.<\/strong><\/p>\n\n\n\n
Also note that, at the release of SQL Server 2025, vector indexing was shipped as a preview feature<\/a>. Therefore, you need to enable preview features on the server to use them.<\/p>\n\n\n\n
The VECTOR_SEARCH() function in SQL Server<\/h2>\n\n\n\n
It’s all very well to have this index structure, but how do we use it? The answer is the VECTOR_SEARCH()<\/code> function. Here’s an example of it in use:<\/p>\n\n\n\n
DECLARE @UserEmbeddings vector(384) = AI_GENERATE_EMBEDDINGS('Natural tomatoes from Italy' USE MODEL AllMiniLM);\n\nSELECT TOP(5) p.ProductID, p.ProductName\nFROM VECTOR_SEARCH\n(\n    TABLE = dbo.Products AS p,\n    COLUMN = ProductNameEmbeddings,\n    SIMILAR_TO = @UserEmbeddings,\n    METRIC = 'cosine',\n    TOP_N = 5\n) AS vs\nORDER BY vs.distance;\nGO<\/pre><\/div>\n\n\n\n
I’ve started with the same calculation of text embeddings for a user query about tomatoes. I’ve then asked for a TOP(5)<\/code> from the vector search function. I tell it the table, column, metric, and TOP_N<\/code> values, along with the value that I want to compare to. <\/p>\n\n\n\n
On my system, this returns exactly the same output that the vector distance query did. However, the big difference here is in speed: this method would be drastically faster when dealing with a lot of data.<\/p>\n\n\n\n
What’s the problem with the vector index in SQL Server?<\/h2>\n\n\n\n
One thing that might not have been immediately obvious is the effect that creating the vector index had on the table. Unfortunately, when a vector index is present, tables are read-only.<\/p>\n\n\n\n
It’s not a huge issue if the data you are searching is relatively static, but for data that’s changing, it’s a problem. And it takes a lot of planning to work around. If you worked with the first version of columnstore indexes<\/a> back in SQL Server 2012, you’ll understand how challenging this is.<\/p>\n\n\n\n
The good news is that Microsoft was aware of how big an issue this would be and was already planning how to fix it. And you’re likely already seeing the changes if you’re using Azure SQL Database<\/a>.<\/p>\n\n\n\n
At first, the vector indexing in Azure SQL Database had the same limitations as the implementation in SQL Server. And if you have vector indexes that you created in Azure SQL Database earlier, they still apply.<\/p>\n\n\n\n
Now, however, if you’re in a region where the new changes have already been rolled out, things are very different. There’s a new version of vector index<\/a>. The syntax is the same, but executing it creates a new type of index. The brilliant part is that the read-only limitation on tables has been removed, too.<\/p>\n\n\n\n
There’s just one catch<\/h3>\n\n\n\n
This is great news but there is one catch. When you have the new style of indexes, you need to use different syntax<\/strong> for vector search. Let’s compare the two.<\/p>\n\n\n\n
The previous syntax is like what I showed you above:<\/p>\n\n\n\n
SELECT TOP (10)\n       p.ProductID,\n       p.[Description]\nFROM VECTOR_SEARCH(\n         TABLE = Sales.Products AS p,\n         COLUMN = DescriptionVector,\n         SIMILAR_TO = @Embeddings,\n         METRIC = 'cosine',\n         TOP_N = 10\n     ) AS vs\nORDER BY vs.distance;<\/pre><\/div>\n\n\n\n
And here’s the new syntax:<\/p>\n\n\n\n
SELECT TOP (10) WITH APPROXIMATE\n       p.ProductID,\n       p.[Description]\nFROM VECTOR_SEARCH(\n         TABLE = Sales.Products AS p,\n         COLUMN = DescriptionVector,\n         SIMILAR_TO = @Embeddings,\n         METRIC = 'cosine'\n     ) AS vs\nORDER BY vs.distance;<\/pre><\/div>\n\n\n\n
Previously, we had the TOP<\/code> details inside the vector search and in the outer query. Now, it’s only<\/em> in the outer query, but requires the keywords WITH APPROXIMATE<\/code>.<\/p>\n\n\n\n
Under the covers, there’s a good reason for this change. It’s mostly about where<\/em> the filtering occurs: whether it’s during the vector search or only applied later. With the new syntax, it’s applied within <\/em>the vector search operation – providing more consistent and faster results.<\/p>\n\n\n\n
While we can’t use these new vector indexes in our on-premises SQL Server systems yet, I’m sure that a future release will provide similar options to those that now exist in Azure SQL Database.<\/strong><\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Write accurate SQL faster in SSMS with SQL Prompt AI<\/h3>\n                
\n                                            Write or modify queries using natural language, get clear explanations for unfamiliar code, and fix and optimize SQL with ease – all without leaving SSMS.                                    <\/div>\n            <\/div>\n                                            Learn more and try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
\n    
FAQs: Vector search in SQL Server<\/h2>\n\n                        
1. What is the VECTOR_DISTANCE function in SQL Server?<\/h3>\n            
\n                
VECTOR_DISTANCE compares text embeddings to find semantically similar rows, using distance metrics like cosine, Euclidean, or dot product.<\/p>\n            <\/div>\n                    
2. When should I use a vector index in SQL Server?<\/h3>\n            
\n                
Use a vector index when querying large tables \u2014 it makes vector search dramatically faster, though it currently ships as a preview feature in SQL Server 2025 and makes tables read-only.<\/p>\n            <\/div>\n                    
3. What is the VECTOR_SEARCH function and how does it differ from VECTOR_DISTANCE?<\/h3>\n            
\n                
VECTOR_SEARCH uses a vector index to return approximate nearest-neighbour results at scale, while VECTOR_DISTANCE scans every row \u2014 making VECTOR_SEARCH far faster on large datasets.<\/p>\n            <\/div>\n                    
4. Why does adding a vector index make a SQL Server table read-only?<\/h3>\n            
\n                
It’s a current limitation of the diskann index type in SQL Server 2025. Microsoft is aware of it \u2014 the restriction has already been lifted in updated Azure SQL Database regions.<\/p>\n            <\/div>\n                    
5. What changed in the Azure SQL Database vector search syntax?<\/h3>\n            
\n                
The new syntax moves TOP out of VECTOR_SEARCH and into the outer query with the WITH APPROXIMATE keyword, improving filtering performance and result consistency.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
Learn how to use VECTOR_DISTANCE and VECTOR_SEARCH in SQL Server to find semantically similar data. Covers vector indexes, performance trade-offs, and the latest Azure SQL Database syntax changes.…<\/p>\n","protected":false},"author":346483,"featured_media":106044,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527,143523,53,143524],"tags":[4168,4170,159401,4150,4151],"coauthors":[159368],"class_list":["post-111121","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-sql-server","category-databases","category-featured","category-sql-server","tag-database","tag-database-administration","tag-greglowollamaseries","tag-sql","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/111121","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/346483"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=111121"}],"version-history":[{"count":12,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/111121\/revisions"}],"predecessor-version":[{"id":111422,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/111121\/revisions\/111422"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/106044"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=111121"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=111121"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=111121"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=111121"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}