The vector data type and similarity search
Semantic search finds rows by meaning instead of keywords. It works by storing an embedding, a list of numbers produced by an AI model, next to each row and comparing distances.
The vector data type
The native vector type is available in Azure SQL Database, SQL Server 2025 and SQL database in Microsoft Fabric. You declare the dimension count:
CREATE TABLE dbo.Products (
ProductId int PRIMARY KEY,
Description nvarchar(1000),
Embedding VECTOR(1536)
);Key facts:
- The dimension count must equal what the embedding model returns (for example 1536 for text-embedding-3-small, 3072 for text-embedding-3-large).
- Values are stored in an efficient binary form and exposed as JSON arrays, such as
'[0.1, -0.3, ...]'. - Do not store embeddings in
nvarchar(max)orvarbinary. You lose type checks and vector functions.
Exact search with VECTOR_DISTANCE
VECTOR_DISTANCE(metric, v1, v2) returns a distance for one pair of vectors.
SELECT TOP (10) ProductId,
VECTOR_DISTANCE('cosine', Embedding, @q) AS dist
FROM dbo.Products
ORDER BY dist;It scans every row, so results are exact (k-nearest neighbours) and it needs no index. It is fine for small tables or when a WHERE clause reduces the rows first.
Approximate search with VECTOR_SEARCH
VECTOR_SEARCH returns the nearest rows using a vector index (DiskANN-based). It is approximate (ANN) and much faster on large tables.
SELECT t.ProductId, s.distance
FROM VECTOR_SEARCH(
TABLE = dbo.Products AS t,
COLUMN = Embedding,
SIMILAR_TO = @q,
METRIC = 'cosine',
TOP_N = 10) AS s;| VECTOR_DISTANCE | VECTOR_SEARCH | |
|---|---|---|
| Result | Exact | Approximate |
| Index | None needed | Vector index |
| Best for | Small sets, filtered sets | Large tables |
Distance metrics
| Metric | Use |
|---|---|
| cosine | Angle between vectors. The usual choice for text embeddings |
| euclidean | Straight-line distance |
| dot | Negative dot product. Good for normalised vectors |
The metric in the query should match the metric the vector index was built with, and the one the model was trained for.
Changing the embedding model
A different model means different dimensions and a different vector space. Existing embeddings are no longer comparable. You must regenerate embeddings for all rows, alter the column dimension, and rebuild the vector index.
Exam traps
- Cosine similarity and cosine distance are opposite directions: smaller distance means closer.
- VECTOR_SEARCH is not exact. If the question demands exact results, choose VECTOR_DISTANCE.
- Normalising vectors does not fix a model change. Only re-embedding does.
- The type name is vector, not a table type or a JSON column.
Practice questions
Choose an answer, then review the explanation to check your understanding.
1. A table stores embeddings from a model that returns 1536 dimensions. Which column definition is correct?
Show answer
Answer: A.The dimension count is part of the VECTOR(n) type and must match the model output.
2. You need the exact distance between a stored vector and a query vector for one row, using cosine. Which function do you use?
Show answer
Answer: B.VECTOR_DISTANCE returns the distance between two vectors for a chosen metric, so it can be used in SELECT or ORDER BY.
3. A large table needs fast approximate nearest-neighbour results. What must exist for VECTOR_SEARCH to be efficient?
Show answer
Answer: B.VECTOR_SEARCH performs approximate nearest-neighbour (ANN) search and uses a vector index. Without one, use exact search with VECTOR_DISTANCE.
4. Rows were embedded with one model and the application now embeds queries with a model that has a different dimension count. What happens if you compare them?
Show answer
Answer: B.Vectors compared must have the same dimensions, and embeddings from different models are not comparable. Re-embed the stored rows.