Vector Data Type, Vector Functions, Similarity Workloads, and Data Modeling Choices

Learning outcomes

ServiceHub wants “find tickets like this one,” but similarity is not a normal equality or range predicate. SQL Server 2025 adds a first-class vector data type and vector functions so an embedding can live beside the relational row it describes. That does not turn the database into a model server, and it does not make every nearest-neighbor search indexed. The first design decision is to separate vector storage, exact distance calculation, and approximate indexed search as different capabilities with different maturity and cost.

1. A vector column is typed data, not an opaque JSON string

SQL Server stores vectors in an optimized binary representation while exposing convenient JSON-array syntax for input and display. A declaration such as vector(4) fixes the dimensional contract at the schema boundary. That is important because two embeddings are only comparable when their dimensions and semantic model contract agree. Keeping an embedding model name, dimension count, content hash and generation timestamp next to the vector makes stale or mixed-model rows observable.

USE master;GOIF DB_ID(N'ServiceHubAILab') IS NULLBEGIN    CREATE DATABASE ServiceHubAILab;END;GOALTER DATABASE ServiceHubAILab SET COMPATIBILITY_LEVEL = 170;GOUSE ServiceHubAILab;GOIF SCHEMA_ID(N'lab23') IS NULL EXEC(N'CREATE SCHEMA lab23 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'lab23.KnowledgeItem', N'U') IS NULLBEGIN    CREATE TABLE lab23.KnowledgeItem    (        item_id               int NOT NULL CONSTRAINT PK_lab23_KnowledgeItem PRIMARY KEY,        region_code           char(3) NOT NULL,        category              varchar(20) NOT NULL,        is_active             bit NOT NULL,        source_text           nvarchar(600) NOT NULL,        embedding_model       nvarchar(100) NOT NULL,        embedding_dimensions  smallint NOT NULL,        content_hash          varbinary(32) NOT NULL,        embedded_at           datetime2(0) NOT NULL,        embedding             vector(4) NOT NULL    );    INSERT lab23.KnowledgeItem        (item_id, region_code, category, is_active, source_text,         embedding_model, embedding_dimensions, content_hash, embedded_at, embedding)    VALUES      (1,'N01','network',1,N'VPN access fails after a password change.',N'lab-mock-v1',4,HASHBYTES('SHA2_256',N'VPN access fails after a password change.'),SYSUTCDATETIME(),'[0.90,0.10,0.05,0.00]'),      (2,'N01','network',1,N'Cannot connect to the office wireless network.',N'lab-mock-v1',4,HASHBYTES('SHA2_256',N'Cannot connect to the office wireless network.'),SYSUTCDATETIME(),'[0.82,0.18,0.05,0.02]'),      (3,'W02','billing',1,N'Invoice total does not match the approved work order.',N'lab-mock-v1',4,HASHBYTES('SHA2_256',N'Invoice total does not match the approved work order.'),SYSUTCDATETIME(),'[0.05,0.88,0.10,0.04]'),      (4,'W02','billing',1,N'Customer requests a corrected tax line on an invoice.',N'lab-mock-v1',4,HASHBYTES('SHA2_256',N'Customer requests a corrected tax line on an invoice.'),SYSUTCDATETIME(),'[0.08,0.79,0.18,0.03]'),      (5,'E03','hardware',1,N'Field laptop battery drains before the technician shift ends.',N'lab-mock-v1',4,HASHBYTES('SHA2_256',N'Field laptop battery drains before the technician shift ends.'),SYSUTCDATETIME(),'[0.06,0.12,0.92,0.04]'),      (6,'E03','hardware',0,N'Retired printer driver causes a paper-size mismatch.',N'lab-mock-v1',4,HASHBYTES('SHA2_256',N'Retired printer driver causes a paper-size mismatch.'),SYSUTCDATETIME(),'[0.09,0.08,0.76,0.10]'),      (7,'N01','account',1,N'Multi-factor authentication approval is not arriving.',N'lab-mock-v1',4,HASHBYTES('SHA2_256',N'Multi-factor authentication approval is not arriving.'),SYSUTCDATETIME(),'[0.69,0.22,0.03,0.12]'),      (8,'W02','account',1,N'New technician cannot see assigned ServiceHub tickets.',N'lab-mock-v1',4,HASHBYTES('SHA2_256',N'New technician cannot see assigned ServiceHub tickets.'),SYSUTCDATETIME(),'[0.42,0.29,0.08,0.55]');END;GO

USE ServiceHubAILab;GOSELECT    c.name AS column_name,    c.vector_dimensions,    c.vector_base_type_descFROM sys.columns AS cWHERE c.object_id = OBJECT_ID(N'lab23.KnowledgeItem')  AND c.name = N'embedding';SELECT TOP (3)    item_id,    CAST(embedding AS nvarchar(max)) AS embedding_as_json,    VECTORPROPERTY(embedding, 'Dimensions') AS dimensions,    VECTORPROPERTY(embedding, 'BaseType') AS base_typeFROM lab23.KnowledgeItemORDER BY item_id;

The schema reports four dimensions and the default float32 base type. In a production embedding model the dimension is often much larger, but the same rule applies: changing the model can change vector meaning even when the dimension stays identical. A four-dimensional teaching vector is deliberately small so the geometry can be inspected without requiring an external model endpoint.

2. Exact similarity is a calculation over every qualifying row

VECTOR_DISTANCE calculates an exact distance. For cosine and Euclidean distance, smaller means closer. For SQL Server's dot metric, the function returns a negative dot-product-based distance, so smaller again represents greater similarity in the ordering contract. The function does not automatically use a vector index. If you order all qualifying rows by exact distance, SQL Server must evaluate that expression for those rows.

USE ServiceHubAILab;GODECLARE @query vector(4) = '[0.86,0.14,0.04,0.01]';SELECT TOP (4)    item_id,    region_code,    category,    source_text,    VECTOR_DISTANCE('cosine', @query, embedding) AS cosine_distanceFROM lab23.KnowledgeItemWHERE is_active = 1  AND region_code = 'N01'ORDER BY cosine_distance, item_id;

The relational predicates are not decoration. They define the candidate set before the vector ranking becomes useful to the application. In a multi-tenant system, tenant/security filtering must remain mandatory even when a vector is semantically close. A “nearest vector” from the wrong tenant is still unauthorized data.

DECLARE @v vector(4) = '[3,4,0,0]';SELECT    VECTOR_NORM(@v, 'norm2') AS euclidean_norm,    CAST(VECTOR_NORMALIZE(@v, 'norm2') AS nvarchar(max)) AS normalized_vector;

Normalization can be part of an embedding contract, but do not apply it blindly. Some model providers already return normalized vectors, some similarity metrics do not require pre-normalization, and changing stored vectors later changes the meaning of previously measured distances.

3. A deliberately wrong model contract

A common migration mistake is to generate new embeddings with a different model and quietly overwrite the old vectors. If the new model changes dimension, SQL Server catches the shape mismatch. If it produces the same dimension but a different semantic space, SQL Server cannot know they are incomparable; provenance columns and deployment governance must catch it.

USE ServiceHubAILab;GO-- Intentionally wrong: a vector(3) cannot be assigned to the vector(4) column.BEGIN TRY    UPDATE lab23.KnowledgeItem      SET embedding = CAST('[0.1,0.2,0.3]' AS vector(3))    WHERE item_id = 1;END TRYBEGIN CATCH    SELECT ERROR_NUMBER() AS error_number, ERROR_MESSAGE() AS error_message;END CATCH;GO-- Repair: keep the declared dimension and preserve model provenance.UPDATE lab23.KnowledgeItemSET embedding = CAST('[0.90,0.10,0.05,0.00]' AS vector(4)),    embedding_model = N'lab-mock-v1',    embedding_dimensions = 4WHERE item_id = 1;

The more dangerous case is the same dimension with a different model version. That is why embedding_model, source hash and generated timestamp are first-class data, not notes in a deployment ticket.

4. GA versus preview matters to architecture

As of the current SQL Server 2025 CU7 documentation, float32 vector storage and the core scalar vector functions are generally available. CREATE VECTOR INDEX and VECTOR_SEARCH remain preview and require PREVIEW_FEATURES. Half-precision float16 vectors are also preview. Preview features are intended for development/testing rather than production-default architecture, and their syntax/limitations can change in cumulative updates.

5. Production judgment

Keep vectors in SQL Server when transactional metadata, security predicates, relational filters and vector ranking belong in one consistency/authorization boundary and the measured workload fits. Separate them when vector scale, independent scaling, specialized indexing, ingestion rate or search features justify a dedicated service. The correct answer is not ideological; Lesson 5 builds an evidence contract for the decision.

Authoritative references

• Vector data type
• Vector functions
• VECTOR_DISTANCE
• VECTORPROPERTY
• Vector search and vector indexes
• Preview features FAQ