SQL Server 2025 stores vectors natively. A vector column holds an embedding, VECTOR_DISTANCE measures how close two embeddings are, and the whole search runs inside the database that already holds your orders, tickets, and documents. There is no second datastore to license and no nightly job keeping two copies of the same records in step.
The feature landed in two halves, and the split decides how much of it you can ship this quarter. One half is generally available. The other needs a preview flag and carries a deployment constraint that tends to surface late.
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. It works under all database compatibility levels, which means adopting it does not force a compatibility-level change on a database you would rather leave alone.
VECTOR_DISTANCE is generally available alongside it and supports three metrics: cosine, euclidean, and dot, where dot returns the negative dot product so smaller still means closer. One behaviour is worth knowing before you design around it. 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 graph, and the VECTOR_SEARCH function that queries it. On SQL Server 2025 both require ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON before they will run. Half-precision float16 vectors sit behind the same flag.
Fifty Thousand Vectors Is the Number That Decides the Design
The Microsoft guidance is specific: exact search is recommended when you have fewer than roughly 50,000 vectors to search, which the documentation frames as a general recommendation. The sentence after it is the one that changes designs. The table may hold far more than that, so long as the search predicates reduce the set of vectors actually compared to 50,000 or fewer.
That clause covers a large share of business workloads. A support desk searching 30,000 knowledge articles is under the threshold outright. A multi-tenant portal holding four million embedded documents is under it too, because every query already carries a tenant filter that cuts the candidate set down to one account. A property system scoped to one market, or a case system scoped to open matters, lands in the same place. For all of those, exact search with VECTOR_DISTANCE is the design, and the preview status of the vector index never enters the conversation.
The Vector Index Will Not Come Through Your DacPac
This is the constraint that catches teams with an established release pipeline. A vector index requires at least 100 rows with non-NULL vector values before it can be created. Attempting it on a smaller table fails with Msg 42266, which reports the row count it found and the 100 it wanted.
Now consider how a DacPac or BACPAC import runs. It creates schema objects first, then loads the data. The vector index is a schema object, so it gets created against a table that is still empty, the 100-row precondition is not met, and the import fails with it. Microsoft documents the outcome plainly: vector indexes cannot be deployed with DacPac or BACPAC. The stated workaround is to drop the vector indexes before exporting and recreate them once the import has finished.
For a shop that publishes database changes as a DacPac through SSDT, SqlPackage, or Microsoft.Build.Sql, that is a pipeline change rather than a schema change. The index stops being an object the tooling manages on your behalf and becomes a post-deployment step with a data precondition attached to it. Decide who owns that step before the first release.
Several related limits belong in the same conversation:
- The table must have a primary key clustered index.
- Vector indexes cannot be partitioned, and they are not replicated to subscribers.
- TRUNCATE TABLE is blocked on a table carrying a vector index. Clearing it means dropping the index, truncating, reloading at least 100 rows, then recreating.
- The newest index version is currently available only in Azure SQL Database and SQL database in Microsoft Fabric. Earlier versions leave the table read-only after the index is created, unless the ALLOW_STALE_VECTOR_INDEX scoped configuration is set and stale search results are acceptable.
Do not assume which version you have. The build_parameters column in sys.vector_indexes carries it, the documentation supplies a query for reading it, and 3 is the current version.
What the Application Code Changes
For clients that do not speak the newer protocol, SQL Server continues to expose vector columns as varchar(max) holding a JSON array. Existing drivers keep working untouched, 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 moves the data over TDS in binary form instead of parsing JSON on every read. Reading a column becomes a call to GetSqlVector<float> on the reader. On .NET 10 the parameter type is SqlDbType.Vector; on earlier versions the same thing is spelled SqlDbTypeExtensions.Vector.
Generating the embeddings is still yours to design. Something has to produce a vector before the database can store it, and where that call happens, how it is retried, and what you do when the model version changes are all application decisions.
Where IKRC Fits
Most of the work in a project like this is not the vector column. It is deciding what gets embedded, where the embedding call lives, how the pipeline deploys an index that has a data precondition, and whether the search stays under the exact-search threshold once real volume arrives. IKRC works at that layer, across .NET development, software integration, and the database design underneath both.
If you are weighing a separate vector database against the SQL Server you already run, your row count and the filter you would apply are usually enough to settle it. Call IKRC at 646-783-1441 or email info@ikrc.co.
Related Reading
For a related look at exposing your own systems to AI tools, read Customer-Facing MCP Servers.
Need this solved in your software?
IKRC builds the custom systems, integrations, and modernization work discussed in this article.