SQL Server 2025 stores vectors natively. A vector column holds an embedding, VECTOR_DISTANCE measures how close two embeddings are, and the search runs inside the database that already holds the orders, tickets and documents being searched. There is no second datastore to license or keep in step with the source tables, although generating and refreshing the embeddings is still a job of its own.
The feature arrived in two halves. One half is generally available. The other needs a preview flag, has conditions on the data before an index can exist, and cannot be deployed through a DacPac or BACPAC.
Which Half Is Generally Available?
The vector data type is generally available in SQL Server 2025. Each element is a single-precision float taking four bytes, a column can carry between 1 and 1998 dimensions, and values go in and come out as JSON arrays, so casting a JSON_ARRAY result to VECTOR(3) is a valid way to build one. The type works under all database compatibility levels, so adopting it does not force a compatibility-level change.
VECTOR_DISTANCE is generally available alongside it and supports three metrics: cosine, euclidean and dot, where dot returns the negative dot product so a smaller value still means closer. VECTOR_DISTANCE is always exact and never uses a vector index, even when a suitable index exists on the column.
The approximate side is preview: CREATE VECTOR INDEX, which builds a DiskANN index, and the VECTOR_SEARCH function that queries it. On SQL Server 2025 both require ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON. Half-precision float16 vectors sit behind the same flag.
Where the 50,000 Figure Comes From
Microsoft's vector search guidance recommends exact search when there are fewer than about 50,000 vectors to search, and calls that a general recommendation. The table can hold more, as long as the query's predicates reduce the vectors actually compared to around that number.
The figure is a starting point for a measurement. The number that matters is the candidate count after filtering, for the largest case the application has to serve. A tenant filter on a multi-tenant table helps only as far as it shrinks that count, so one large tenant can put a design over the line even when the average tenant is well under it. Dimensions matter too: at four bytes per element, a 1536-dimension embedding is about 6 KB of vector data per row before anything else is read. Latency and CPU for exact search on representative data, at the dimensions the embedding model actually produces, decide whether VECTOR_DISTANCE alone is enough.
Filters and the Approximate Index
The filtering detail also changes how the preview index behaves. Microsoft's documentation describes two generations of vector index. Earlier versions apply relational predicates only after the vector search has returned its nearest neighbors. A query for the ten closest documents belonging to one tenant can therefore come back with fewer than ten rows, or none, when the closest matches across the whole table belong to other tenants. Earlier versions also leave the table read-only after the index is created, unless the ALLOW_STALE_VECTOR_INDEX database scoped configuration is set and stale results are acceptable.
The latest version, reported as 3, applies predicates during the search and allows normal INSERT, UPDATE and DELETE. As of the current VECTOR_SEARCH documentation, that version is available only in Azure SQL Database and SQL database in Microsoft Fabric, with regional rollout still in progress. The version a given index uses can be read from build_parameters in sys.vector_indexes. For a filtered workload, that query is worth running before the approximate index is trusted with production search.
The Vector Index Will Not Come Through Your DacPac
As documented in September 2026 for SQL Server 2025, where vector indexes are preview, and for Azure SQL Database and SQL database in Fabric, a vector index needs at least 100 rows with non-NULL vector values before it can be created. On a smaller table the CREATE fails with Msg 42266, which reports the row count it found and the 100 it needs.
The CREATE VECTOR INDEX page explains that a DacPac, BACPAC or Import/Export import creates schema objects, including vector indexes, before it loads data, so the index meets an empty table and the import fails. It states that vector indexes cannot be deployed with DacPac or BACPAC, and gives the workaround: drop the vector indexes before export and recreate them after import. For a team that publishes schema through SSDT, SqlPackage or Microsoft.Build.Sql, the index becomes a post-deployment script that checks the row count before it runs CREATE VECTOR INDEX.
The same page lists the other current limits, all of which apply to the latest version too:
- The table must have a clustered primary key on an int column.
- Vector indexes cannot be partitioned and are not replicated to subscribers.
- TRUNCATE TABLE is blocked while the index exists. Clearing the table means dropping the index, truncating, reloading at least 100 rows and recreating it.
These are preview limits as documented in September 2026, and they should be checked again against the page before an index design is fixed.
What the Application Code Changes
For clients that do not support the newer protocol, SQL Server exposes vector columns as varchar(max) holding a JSON array. Existing drivers keep working, and code that serializes a float array to JSON and reads it back still functions.
Native handling needs Microsoft.Data.SqlClient 6.1.0 or later, which introduces SqlVector<T> and sends vectors over TDS in binary form, so the JSON is not parsed on every read. On .NET 10 the parameter type is SqlDbType.Vector. Earlier .NET versions use SqlDbTypeExtensions.Vector.
Storing vectors in SQL Server does not remove the embedding job. Something still has to generate a vector whenever a row is added or its text changes. A change of embedding model needs a planned transition as well: queries have to be embedded with the same model as the vectors they search, so the query path and the stored corpus move together. One way is to fill a new vector column in batches and point queries at it, with the new model, once it is populated. If the search uses an approximate index, the new column needs its own CREATE VECTOR INDEX under the limits above. Where embeddings are replaced in place instead, the CREATE VECTOR INDEX page advises dropping and recreating the index after the load, because an index built for the old embedding distribution can lose recall and ranking quality, and on an earlier index version the table stays read-only while the old index exists unless ALLOW_STALE_VECTOR_INDEX is set.
Related Reading
For generating the embeddings inside the database, read SQL Server 2025 as an AI Platform, Not Just a Vector Store.