The vector data type

On the first morning of the prototype, Hana's question was practical: where do the 1,536 numbers for each product go? Before SQL Server 2025, teams stored embeddings as JSON text in an nvarchar(max) column or as binary in varbinary(max), and wrote their own functions to parse them and compute distances. Both worked, slowly. Text is large, every comparison had to parse it, and nothing stopped a row from holding 1,535 numbers by mistake. The vector data type replaces those workarounds with a column type the engine understands.

Declaring a vector column

A vector column is declared with the number of dimensions it holds, and optionally the base type of each element. The default base type is float32, a 4-byte single-precision number. Every value stored in the column must have exactly the declared number of dimensions; a value with the wrong count is rejected with an error rather than stored. That check alone removed a class of bugs Hana had met in an earlier project, where embeddings from two different models had been mixed in one column.

CREATE TABLE catalog.ProductEmbedding
(
    ProductId     int            NOT NULL
        CONSTRAINT PK_ProductEmbedding PRIMARY KEY CLUSTERED,
    ModelName     varchar(64)    NOT NULL,
    EmbeddedAt    datetime2(0)   NOT NULL,
    SourceHash    binary(32)     NOT NULL,   -- hash of the text that was embedded
    Embedding     vector(1536)   NOT NULL    -- float32 by default
);

Vectors are written and displayed as JSON arrays of numbers, such as [0.012, -0.044, 0.318]. Inside the database they are stored in a compact binary format, and modern drivers, such as Microsoft.Data.SqlClient 6.1 and later with its SqlVector type, can send and receive them in that binary form. Older drivers receive vectors as JSON text in a varchar(max) value, which still works without code changes, only less efficiently.

INSERT INTO catalog.ProductEmbedding (ProductId, ModelName, EmbeddedAt, SourceHash, Embedding)
VALUES (114, 'text-embedding-3-small', SYSUTCDATETIME(), HASHBYTES('SHA2_256', N'...'),
        CAST(@embeddingJson AS vector(1536)));

-- A three-dimensional example to see the formats
DECLARE @v vector(3) = '[1, 2.5, -0.25]';
SELECT CAST(@v AS nvarchar(200)) AS as_text,   -- scientific notation
       CAST(@v AS json)          AS as_json;   -- decimal notation

Half precision

The optional base type float16 stores each element in half precision, 2 bytes instead of 4. A float16 column can hold up to 3,996 dimensions, against 1,998 for float32. Half precision halves storage and memory, and for most embedding models the small loss of precision makes no visible difference to search quality, but that should be tested rather than assumed. As of November 2026, float16 is generally available in Azure SQL Database, Managed Instance and SQL database in Fabric, and a preview feature in SQL Server 2025, where it needs the PREVIEW_FEATURES database scoped configuration turned on.

-- Azure SQL Database: no configuration needed
ALTER TABLE catalog.ProductEmbedding ADD Embedding16 vector(1536, float16) NULL;

-- SQL Server 2025: preview features must be enabled in the database first
ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON;

The dimension limits matter when choosing a model. A model that returns 3,072 dimensions, such as the larger OpenAI embedding model, does not fit in a float32 vector column at all, because 3,072 is more than 1,998. There are two ways out: store it as float16, which allows up to 3,996, or ask the model for fewer dimensions, which newer embedding models support through a dimensions parameter. Hana's model returns 1,536 dimensions by default and fits either way.

What the type does not allow

The vector type is deliberately narrow. Only NULL and NOT NULL constraints are allowed on a vector column: no DEFAULT, CHECK, PRIMARY KEY, FOREIGN KEY or UNIQUE constraint. There are no arithmetic or comparison operators, so a query cannot add two vectors with a plus sign or test them with an equals sign; it uses vector functions instead. A vector column cannot be the key of a B-tree or columnstore index, although it can be an included column, and it cannot be used in memory-optimized tables, alias types, sql_variant or Always Encrypted columns. Approximate search uses a separate kind of index, the vector index, covered in lesson 6.

Two of these limits surprised Hana. The first was ledger: a database that contains a table with a vector column cannot run ledger verification successfully, so vector columns should not go into a database that depends on ledger tables for tamper evidence. The Finch Rewards database uses ledger for points history, as MSAZ-281 described, which is one reason the embeddings live in the catalog database instead. The second was Always Encrypted: an encrypted vector column is not supported, so embeddings of sensitive text need other protection, discussed in lesson 10.

Conversion rules and errors

Conversions follow strict rules. A string or JSON value converts to a vector only if it is a JSON array of numbers with exactly the declared count; a count mismatch raises an error, as does any other invalid input. Converting a vector to text gives scientific notation, while converting to JSON gives decimal notation. A vector converts to another vector only when both the dimensions and the base type match. And if a vector is converted to a string column that is too short to hold it, the conversion fails rather than truncating the value, which protects against silently storing half an embedding.

Because vectors have lower data type precedence than strings, mixing them in CASE, COALESCE or UNION expressions produces a string result. Hana met this when a COALESCE between a new embedding and a fallback string produced nvarchar instead of vector; the fix was to cast both branches explicitly.

Reading vector properties

The VECTORPROPERTY function returns metadata about a vector value, such as its number of dimensions and its base type, which is useful in validation and in code that handles columns from different models. Hana uses it in a check that runs after each embedding load, to confirm that every row in a staging table has the expected shape before rows are moved into the main table.

SELECT TOP (5) ProductId,
       VECTORPROPERTY(Embedding, 'Dimensions') AS dims,
       VECTORPROPERTY(Embedding, 'BaseType')   AS base_type
FROM catalog.ProductEmbedding;

How much space vectors take

Storage is easy to estimate because each element has a fixed size: 4 bytes for float32 and 2 for float16, plus a small fixed header per value. Elena asked for the numbers before the test database grew, because vectors are large compared with the rows they describe. A product row in the catalog averages about 600 bytes; its embedding is ten times that.

products = 18_400      # product descriptions
chunks = 10_416        # guide sections after chunking (lesson 3)
dims = 1536

for name, size in [("float32", 4), ("float16", 2)]:
    per_vector = dims * size
    total = (products + chunks) * per_vector
    print(f"{name}: {per_vector:,} bytes per vector, "
          f"{total / 1024**2:,.1f} MiB for {products + chunks:,} vectors")

# A 3,072-dimension model would not fit in float32 (limit 1,998)
for d in (1536, 3072):
    print(d, "dims fits float32:", d <= 1998, " fits float16:", d <= 3996)

float32: 6,144 bytes per vector, 168.8 MiB for 28,816 vectors
float16: 3,072 bytes per vector, 84.4 MiB for 28,816 vectors
1536 dims fits float32: True  fits float16: True
3072 dims fits float32: False  fits float16: True

About 169 MiB is small for Azure SQL Database, but it is not the whole cost. A vector index adds its own structures, and the vectors are read whenever a query computes exact distances. Elena's planning number for the pilot is to allow roughly two to three times the raw vector size for vectors plus the index, and to measure the real figure once the index is built.

Vectors and the rest of the schema

Where to put the vector column is a design decision, not a detail. Hana's first draft added an Embedding column to the Product table itself. Elena talked her out of it for three reasons. The product table is read constantly by the tills and the website, and a 6 KB column on every row would make each page hold far fewer rows, so ordinary queries that never touch the embedding would read more pages. Embeddings are regenerated on their own schedule, by a job, while product rows are edited by people, and separating them avoids lock contention between the two. And a separate table makes it easy to hold embeddings from a second model side by side during a model change, with ModelName as part of the design rather than an afterthought.

The narrow-table design also helps exact search. A query that computes distances reads only the embedding table, whose rows are almost entirely vector, and joins back to Product for the handful of rows it returns. On the test database this halved the pages read by an exact search compared with the wide-table draft.

Guide chunks follow the same pattern: a GuideChunk table holds the guide key, the chunk order, the chunk text, the model name and the vector, with a clustered primary key on a surrogate ChunkId. Keeping the chunk text next to its vector is deliberate. A search returns the passage itself, so the answer procedure in lesson 8 can package it without a second lookup, and the text that was embedded is always the text that is shown.

The last point in that example is easy to miss: two models can produce vectors of the same length that mean entirely different things. A column should record which model produced its vectors, which is why Hana's table has a ModelName column, and a migration to a new model means re-embedding every row, not mixing old and new vectors.