Introduction

SQL Server 2025 introduced new features, including vectors. The main purpose of vectors is to create a new semantic search with the help of AI. Modern AI models represent text as vectors (embeddings) that capture semantic meaning. Similar meanings produce vectors that are close to each other in this vector space, allowing AI systems to compare and reason about language using distance and similarity measures.

In this article, we will focus on the vectors and the functions related to them. Another article will be dedicated to AI-powered Semantic Search.

Requirements

First, we will need SQL Server 2025 or later. At the time this article was written, the last version was SQL Server 2025.

How to create a table in variables

First, we will create a vector with 3 dimensions:

declare @myvector as vector(3) = '[5, 2, 30]'

Secondly, we will show how to create a vector with 4 dimensions:

declare @myvector as vector(3) = '[5, 2, 30, 32]'

Thirdly, we will show that negative and real numbers are supported:

declare @myvector as vector(3) = '[5.22, -2.65, 30.78]'

Finally, we will show how to store vector values using the JSON_ARRAY functions.

declare @myvector as vector(3) = JSON_ARRAY(1.0, 2.0, 3.0)

How to create a table with a vector

The following example shows how to create a table named products with a vector data time included in the Embedding column.

CREATE TABLE Products (
Id INT IDENTITY PRIMARY KEY,
Name NVARCHAR(100),
Embedding VECTOR(3)
)

How to insert values in a table with vectors

In this section, we will show how to insert vector data into the table previously created.

INSERT INTO dbo.Products
(
    Name,
    Embedding
)
VALUES
('Laptop', '[0.9, 0.1, 0.2]'),
('Mouse', '[0.2, 0.8, 0.1]'),
('Keyboard', '[0.3, 0.7, 0.2]');

How to show the value of the first element of a vector

Another common request is to get the first element of a vector. First, we use the OPENJSON function to parse the values. Finally, we use the where to find the first element (the first element is in position 0, the second in position 1, the third in position 2, and so on).

declare @myvector as vector(3) = JSON_ARRAY(1.0, 2.0, 3.0)
SELECT value AS firstvalue
FROM OPENJSON(CAST(@myvector AS NVARCHAR(MAX)))
WHERE  = 0;

The distance functions

In a semantic search, the distance can find the words that are more accurate. It is like a Google Search. Let’s say that you Google the word Japanese food. Imagine that Google is searching for those words. Based on the context in which words appear, each word is represented as a vector. Google then measures the distance between these vectors to determine how related the words are. If two words are closely related, the distance between their vectors is small. If their relationship is weaker or unrelated, the distance between the vectors is larger.

The following example shows how to use the distance between a vector specified and the vectors of the dbo.products table previously created.

DECLARE @myVector VECTOR(3) = '[0.88, 0.12, 0.22]'
SELECT
Name,
VECTOR_DISTANCE('cosine', Embedding, @myVector) AS Distance
FROM Products
ORDER BY Distance;

First, we create a vector that may represent a phrase, and then we compare the distance between that vector and the rows of our table dbo.products.

The laptop is closer to the vector provided, and the mouse is the one with a higher distance. There are 3 options for Vector Distance:

• First, cosine is used for text, OpenAI, Natural Language Processing (NLP), and Retrieval-Augmented Generation (RAG).
• Secondly, Euclidean is used for numbers.
• Finally, dot is used for normalized vectors (Unit-length vector).

The following example shows how to use the Euclidean and dot with the VECTOR_Distance function.

DECLARE @myVector VECTOR(3) = '[0.88, 0.12, 0.22]';

SELECT ProductName,
       VECTOR_DISTANCE('euclidean', Embedding, @myVector) AS EuclideanDistance,
       VECTOR_DISTANCE('dot', Embedding, @myVector) AS DotDistance
FROM Products2;

The VECTOR_NORM function

This function can return 3 main values:

• First, we have the Norm1. It is the 1-Norm, which is the sum of the absolute values of a vector. For example, if your vector has the values [1, 2, 3], the norm1 is 6 (1+2+3).
• Secondly, the Norm2, which is the 2-norm, which is the Euclidean norm, which is the square root of the sum of the squares of the vector components. This Norm2 is used to validate embeddings or normalize them.
• Finally, we have the norminf which is the infinity norm. This is the maximum of the absolute values of the vector components. This is commonly used to detect outliers.

The following example shows how to use them in the VECTOR_NORM function.

DECLARE @myVector VECTOR(3) = '[1, 2, 3]';

SELECT
    -- norm1 (Manhattan):
    -- |1| + |2| + |3| = 6
    VECTOR_NORM(@myVector, 'norm1') AS norm1,
    -- norm2 (Euclidean):
    -- sqrt(1^2 + 2^2 + 3^2)
    -- = sqrt(1 + 4 + 9)
    -- = sqrt(14) ˜ 3.7416
    VECTOR_NORM(@myVector, 'norm2') AS norm2,
    -- norminf (Infinity Norm):
    -- max(|1|, |2|, |3|)
    -- = 3
    VECTOR_NORM(@myVector, 'norminf') AS norminf;

The VECTOR_NORMALIZE function

The function rescales the vector. We normalize vectors to focus on the directions more than the magnitude. For semantic searches, we usually work with normalized values.

This function can return 3 main values:

• First, we have the norm1 that divides the components by the sum of the absolute values. We usually use this normalization for weights and proportions.
• Secondly, we have the norm2 that divides the values by the square root of the sum of the squares of the vector components. This is used for directions and angles.
• Finally, we use the normif to divide the values by the largest absolute component. This normalization is used for bounds and limits.

The following code shows how to use the functions and how to calculate manually the values generated:

DECLARE @myvector AS VECTOR(3) = '[1, 2, 3]';

SELECT
----------------------------------------------------------------------
-- L1 normalization (norm1)
--
-- Step 1: compute L1 norm
-- norm1 = |1| + |2| + |3|
-- = 6
--
-- Step 2: divide each component by norm1
-- [1/6, 2/6, 3/6]
-- ˜ [0.1667, 0.3333, 0.5000]
--
-- Resulting vector has L1 norm = 1
----------------------------------------------------------------------
VECTOR_NORMALIZE(@myvector, 'norm1') AS norm1,
----------------------------------------------------------------------
-- L2 normalization (Euclidean / norm2)
--
-- Step 1: compute L2 norm
-- norm2 = sqrt(1^2 + 2^2 + 3^2)
-- = sqrt(14)
-- ˜ 3.7417
--
-- Step 2: divide each component by norm2
-- [1/3.7417, 2/3.7417, 3/3.7417]
-- ˜ [0.2673, 0.5345, 0.8018]
--
-- Resulting vector has Euclidean length = 1
----------------------------------------------------------------------
VECTOR_NORMALIZE(@myvector, 'norm2') AS norm2,
----------------------------------------------------------------------
-- L8 normalization (norminf / infinity norm)
--
-- Step 1: compute infinity norm
-- norminf = max(|1|, |2|, |3|)
-- = 3
--
-- Step 2: divide each component by norminf
-- [1/3, 2/3, 3/3]
-- ˜ [0.3333, 0.6667, 1.0000]
--
-- Resulting vector has max(|component|) = 1
----------------------------------------------------------------------
VECTOR_NORMALIZE(@myvector, 'norminf') AS norminf;

The VECTORPROPERTY function

Finally, we have this function that detects the vector's data type (basetype) and the number of dimensions (Dimensions).

The following examples show how to get the basetype and dimensions of the vector.

DECLARE @myvector AS VECTOR(4) = '[1,2,3,4]'

SELECT VECTORPROPERTY(@myvector, 'BaseType') as DataType,
       VECTORPROPERTY(@myvector, 'Dimensions') as numberofDimensions

Next Steps

For more information about vectors:

• Vector functions
• Vector data type