--step 1
declare @v1 vector(3) = cast('[1,3,-5]' as vector(3));
declare @v2 vector(3) = cast('[4,-2,-1]' as vector(3));
select
vector_distance('euclidean', @v1, @v2) AS euclidean_distance,
vector_distance('cosine', @v1, @v2) AS cosine_distance,
vector_distance('dot', @v1, @v2) AS dotproduct_distance;
--step 2
IF NOT EXISTS (SELECT * FROM sys.databases WHERE NAME = 'vectordemo')
CREATE DATABASE vectordemo;
GO
USE vectordemo;
GO
CREATE DATABASE vectordemo;
GO
USE vectordemo;
GO
drop table if exists vectorstable
create table vectorstable
(
id int not null identity primary key,
vector vector(3) not null
);
insert into vectorstable (vector)
select
cast(a as vector(3))
from
( values
('[4, -2, -1]'),
('[1, 3, -5]'),
('[7, -8, -10]'),
('[4.0, 0.2, -1.1]'),
('[0, 0, 0]'),
('[10, 10, 10]'),
('[-0.1, -0.2, -0.3]')
) V(a)
;
select id, cast(vector as varchar(100)) as v from vectorstable
--step 3
declare @v vector(3) = cast('[6.4,-2.5,1.3]' as vector(3));
select
v.id,
vector_distance('cosine', @v, v.vector) as distance
from
vectorstable v
where
vector_distance('cosine', @v, v.vector) < 0.5
order by
distance
--step 4
In order to calculate distance, vectors must have the same number of
dimensions. The following query will fail as the query vector
@v has 6 dimensions while the stored vectors have only 3. declare @v vector(6) = cast('[1, 2, 3, 4, 5, 6]' as vector(6));
select
v.id,
vector_distance('cosine', @v, v.vector) as distance
from
vectorstable v
where
vector_distance('cosine', @v, v.vector) < 0.5
order by
distance
