Applies to SQL Server 2025 (and Azure SQL Database and Managed Instance). Not available in SQL Server 2019 or 2022.

The SQL Server vector data type arrived in SQL Server 2025. It holds a fixed-length list of numbers, usually an embedding produced by an AI model, so you can find rows that are “similar” to something without moving the data to a separate search service. I don’t expect most DBAs to build AI applications, but developers will start adding these columns, so it helps to know what they are, how big they get and where people go wrong with them.

Declaring a SQL Server vector data type column

You give the number of dimensions when you declare it, and every value must have exactly that many. Each element is stored as a 4-byte float (float32) by default. The maximum is 1,998 dimensions for float32. A float16 base type halves the storage and allows up to 3,996 dimensions, but in SQL Server 2025 it is still a preview feature and needs the PREVIEW_FEATURES database scoped configuration turned on.

SQL Server stores vectors in its own binary format, but you write and read them as JSON arrays. Strings and the json type convert to and from vector in both directions.

CREATE TABLE dbo.Colours
(
    ColourId   int IDENTITY(1,1) PRIMARY KEY,
    ColourName varchar(20) NOT NULL,
    Rgb        vector(3) NOT NULL
);

INSERT INTO dbo.Colours (ColourName, Rgb)
VALUES ('Red',        '[1.0, 0.0, 0.0]'),
       ('Dark red',   '[0.6, 0.0, 0.0]'),
       ('Orange',     '[1.0, 0.5, 0.0]'),
       ('Green',      '[0.0, 1.0, 0.0]'),
       ('Cyan',       '[0.0, 1.0, 1.0]');

-- Wrong number of elements fails (error 42204)
INSERT INTO dbo.Colours (ColourName, Rgb)
VALUES ('Broken', '[1.0, 0.0]');

Three dimensions keeps the example readable. Real text embeddings are usually 768, 1,536 or 3,072 dimensions; a 1,536-dimension float32 vector is a little over 6 KB a row, so they add up quickly. Note that 3,072 won’t fit as float32.

Comparing vectors with VECTOR_DISTANCE

You can’t use =, <, ORDER BY or arithmetic on a vector column directly. Instead you measure the distance between two vectors with VECTOR_DISTANCE, which takes a metric name and two vectors with the same number of dimensions:

• cosine: the angle between them, from 0 (same direction) to 2 (opposite). This is the usual choice for text embeddings.
• euclidean: straight-line distance, 0 for identical vectors.
• dot: the negative dot product, so that smaller still means closer.

DECLARE @Target vector(3) = '[0.9, 0.1, 0.0]';

SELECT TOP (3)
       ColourName,
       VECTOR_DISTANCE('cosine', @Target, Rgb) AS Distance
FROM dbo.Colours
ORDER BY Distance;   -- smallest first = most similar

This returns Red and Dark red at the top with practically the same distance (they point the same way, only the length differs), then Orange. Cosine ignores length; if you want Dark red to come out further away than Red, use euclidean.

The common mistake: treating distance as similarity

The function returns a distance, not a score. Smaller means more alike, for all three metrics. People coming from other tools that return a similarity score (where 1 means a perfect match) write this:

DECLARE @Target vector(3) = '[0.9, 0.1, 0.0]';

-- Wrong: returns the LEAST similar rows
SELECT TOP (3) ColourName,
       VECTOR_DISTANCE('cosine', @Target, Rgb) AS Distance
FROM dbo.Colours
ORDER BY Distance DESC;

It runs, returns three rows and looks plausible, which is why it survives testing. With real embeddings you can’t eyeball the numbers the way you can with colours, so the bug can go unnoticed for a long time. The dot metric is the easiest to get backwards, because SQL Server negates the dot product: a big positive dot product (very similar) comes back as a big negative number. If you want a similarity figure to show users, work it out yourself (for cosine, 1 - VECTOR_DISTANCE('cosine', ...)) and keep sorting on the distance.

A second mistake is expecting VECTOR_DISTANCE to use a vector index. It doesn’t: it is always an exact search that reads every row it is given. That is fine up to tens of thousands of rows, especially if a normal WHERE clause narrows things down first. For larger sets there are approximate vector indexes (CREATE VECTOR INDEX with VECTOR_SEARCH), but in SQL Server 2025 those are still preview features, so I wouldn’t put them in production yet.

Watch out for

• SQL Server 2025 only. Code that uses vector won’t run on a 2019 or 2022 instance, so check where else the database or its scripts have to go.
• No primary key, unique, CHECK or DEFAULT constraints on a vector column, and no ordinary B-tree or columnstore index (it can be an included column).
• Not supported in memory-optimised tables or with Always Encrypted.
• Older drivers see the column as varchar(max) JSON. That works, but it is bigger on the wire and can lose a little precision. Newer drivers, such as Microsoft.Data.SqlClient 6.1 and later, send it as binary.
• Storage grows with dimensions: plan disk, backup size and memory for it, just as you would for a wide nvarchar column.
• Both vectors passed to VECTOR_DISTANCE must have the same number of dimensions, so store the model name with the data and don’t mix embeddings from different models in one column.

Microsoft’s reference pages for the vector data type and VECTOR_DISTANCE list the limits in full. If you are weighing up a column’s size and type more generally, my notes on T-SQL performance anti-patterns and the SQL Server DBA handbook are a good place to start.

This topic came up in the SQLServerCentral Question of the Day: SQL Server Vector Support I.

Clive Roberts DBA