vector-search-azure-sql

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Vector storage and search on Azure SQL Database

Azure SQL Database 上的向量存储与搜索

The
vector
type behaves like no other column type in this engine, and the query that looks like vector search is not the query that uses the vector index.
Unless a line names a build, every error number and plan below is the same on Azure SQL Database at compatibility level 170 and on the Azure SQL Database container; both report
SERVERPROPERTY('EngineEdition')
5.
在此引擎中,
vector
类型的行为与其他任何列类型都不同;看起来像向量搜索的查询,并不是会使用向量索引的查询。
除非某一行明确指出具体构建版本,否则以下所有错误号和执行计划在兼容级别 170 的 Azure SQL Database 和 Azure SQL Database 容器上均相同;二者报告的
SERVERPROPERTY('EngineEdition')
都是 5。

Scope

适用范围

  • Owns the
    vector
    type and the query surface on Azure SQL Database.
  • The end to end pipeline is
    rag-on-azure-sql
    , generating embeddings is
    embeddings-and-external-models
    , and a vector column's place in a wider design is
    design-azure-sql-schema
    .
  • 涵盖 Azure SQL Database 上的
    vector
    类型及查询接口。
  • 端到端流水线是
    rag-on-azure-sql
    ,生成 embeddings 是
    embeddings-and-external-models
    ,而 vector 列在更广泛设计中的位置属于
    design-azure-sql-schema
    。

Build a test table and the index first

先构建测试表和索引

Do not read about the index. Build one. It is the quickest way to learn how your engine's index behaves.
sql
CREATE TABLE dbo.chunks (
    id        int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    body      nvarchar(400) NOT NULL,
    category  nvarchar(40)  NOT NULL,
    embedding vector(5)     NULL
);
GO
INSERT INTO dbo.chunks (body, category, embedding)
SELECT CONCAT(N'chunk ', value), N'general',
       CAST(JSON_ARRAY(CAST(value * 0.01 AS float), CAST(value * 0.02 AS float),
                       CAST(value * 0.03 AS float), CAST(value * 0.04 AS float),
                       CAST(value * 0.05 AS float)) AS vector(5))
FROM GENERATE_SERIES(1, 150);
GO
CREATE NONCLUSTERED INDEX ix_chunks_category ON dbo.chunks (category);
GO
DECLARE @t0 datetime2(3) = SYSUTCDATETIME();
CREATE VECTOR INDEX vi_chunks ON dbo.chunks (embedding)
    WITH (METRIC = 'cosine', TYPE = 'diskann');
SELECT DATEDIFF(millisecond, @t0, SYSUTCDATETIME()) AS build_ms;
At this size it builds in a fraction of a second, in the container and in the cloud. If a plan, a document or a model says this statement is not yet buildable, run it before believing it. You turn nothing on first. Compatibility level is not the gate: column, insert,
VECTOR_DISTANCE
and approximate search over an existing index all work at level 150.
PREVIEW_FEATURES
is not the gate either:
sql
SELECT [name], [value] FROM sys.database_scoped_configurations WHERE [name] = 'PREVIEW_FEATURES';
Do not read a decision out of that number. In the container it is not deterministic: freshly created databases on one engine read 0 or 1, varying from one database to the next, while
model
and
master
both read 0. Learn documents OFF as the default, scopes the requirement to the boxed engine, and its own
CREATE VECTOR INDEX
example calls the setting "not needed for Azure SQL Database". What matters is the statement above: the index builds and you set nothing. If a document tells you to turn this on first, it is wrong whatever your database happens to report. The type,
VECTOR_DISTANCE
,
VECTOR_NORM
,
VECTOR_NORMALIZE
and
VECTORPROPERTY
are generally available. On Azure SQL Database
CREATE VECTOR INDEX
,
VECTOR_SEARCH
and
TOP (N) WITH APPROXIMATE
are generally available too; they are preview only on SQL Server 2025, behind
PREVIEW_FEATURES
. Check feature availability by region before a design depends on the index.
不要只阅读索引文档。先创建一个。这是了解引擎索引行为最快的方法。
sql
CREATE TABLE dbo.chunks (
    id        int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    body      nvarchar(400) NOT NULL,
    category  nvarchar(40)  NOT NULL,
    embedding vector(5)     NULL
);
GO
INSERT INTO dbo.chunks (body, category, embedding)
SELECT CONCAT(N'chunk ', value), N'general',
       CAST(JSON_ARRAY(CAST(value * 0.01 AS float), CAST(value * 0.02 AS float),
                       CAST(value * 0.03 AS float), CAST(value * 0.04 AS float),
                       CAST(value * 0.05 AS float)) AS vector(5))
FROM GENERATE_SERIES(1, 150);
GO
CREATE NONCLUSTERED INDEX ix_chunks_category ON dbo.chunks (category);
GO
DECLARE @t0 datetime2(3) = SYSUTCDATETIME();
CREATE VECTOR INDEX vi_chunks ON dbo.chunks (embedding)
    WITH (METRIC = 'cosine', TYPE = 'diskann');
SELECT DATEDIFF(millisecond, @t0, SYSUTCDATETIME()) AS build_ms;
在这个数据规模下,无论是在容器还是云中,都只需不到一秒即可构建完成。如果某个执行计划、文档或模型声称此语句尚不可构建,请先亲自运行,不要直接相信。无需事先启用任何功能。兼容级别不是门槛:列、insert、
VECTOR_DISTANCE
以及基于现有索引的近似搜索在级别 150 下均可使用。
PREVIEW_FEATURES
也不是门槛:
sql
SELECT [name], [value] FROM sys.database_scoped_configurations WHERE [name] = 'PREVIEW_FEATURES';
不要仅凭该数值下结论。 在容器中,该值并不确定:同一引擎上新创建的数据库有时返回 0,有时返回 1,不同数据库之间也存在差异,而
model
和
master
均返回 0。Learn 文档将 OFF 记为默认值,并将这一要求限定在框出的引擎范围内;其自身的
CREATE VECTOR INDEX
示例称该设置为 "not needed for Azure SQL Database"。真正重要的是上面的语句:索引可以成功构建,而且你无须设置任何内容。如果某个文档告诉你必须先启用它,那么无论你的数据库恰好报告什么,该文档都是错误的。
vector
类型、
VECTOR_DISTANCE
、
VECTOR_NORM
、
VECTOR_NORMALIZE
和
VECTORPROPERTY
均已正式发布。在 Azure SQL Database 上,
CREATE VECTOR_INDEX
、
VECTOR_SEARCH
和
TOP (N) WITH APPROXIMATE
也已正式发布;只有在 SQL Server 2025 中,它们才需要通过
PREVIEW_FEATURES
启用预览功能。在设计依赖索引之前,请检查各区域的可用功能。

The correction: which query shape reaches the index

修正:哪种查询形态会访问索引

The usual answer to a request for fast similarity search is this. It is wrong and nothing reports it:
sql
-- Correct results. Never uses a vector index. Computes a distance for EVERY row.
DECLARE @q vector(5) = '[0.3,0.3,0.3,0.3,0.3]';
SELECT TOP (10) id, body, VECTOR_DISTANCE('cosine', embedding, @q) AS distance
FROM dbo.chunks
ORDER BY VECTOR_DISTANCE('cosine', embedding, @q);
With a current DiskANN index the plan is
Clustered Index Scan
, then
Compute Scalar
evaluating
vector_distance
per row, then
Sort
. The index is not in the plan. Learn states it outright: vector distance is always exact and doesn't use any vector index, even if available. The shape that does reach the index is a different statement, not a tuning option:
sql
-- Uses the vector index. Vector Index Seek in the plan.
DECLARE @q vector(5) = '[0.3,0.3,0.3,0.3,0.3]';
SELECT TOP (10) WITH APPROXIMATE t.id, t.body, r.distance
FROM VECTOR_SEARCH(TABLE = dbo.chunks AS t, COLUMN = embedding,
                   SIMILAR_TO = @q, METRIC = 'cosine') AS r
WHERE t.category = N'general'
ORDER BY r.distance;
Four shapes on the same table and index:
QueryPlan
ORDER BY VECTOR_DISTANCE(...)
with
TOP
Clustered Index Scan, exact, full scan
VECTOR_SEARCH
with
TOP
and no
WITH APPROXIMATE
Clustered Index Scan, exact kNN
VECTOR_SEARCH
with
TOP (N) WITH APPROXIMATE
Vector Index Seek, approximate
TOP (N) WITH APPROXIMATE
on a table with no vector index
Clustered Index Scan, no error
Every wrong shape returns correct rows. That is the whole hazard. A pipeline tested on a few thousand rows passes, ships, then computes one distance per row per query forever, so the defect shows up as latency and cost, not as a failure anyone can point at. Learn says a warning is raised when no compatible index is found; do not count on seeing one.
Turn the silent fallback into a failure.
WITH (FORCE_ANN_ONLY)
goes on the
VECTOR_SEARCH
alias and needs an index and
WITH APPROXIMATE
present:
sql
DECLARE @q vector(5) = '[0.3,0.3,0.3,0.3,0.3]';
SELECT TOP (10) WITH APPROXIMATE t.id, r.distance
FROM VECTOR_SEARCH(TABLE = dbo.chunks AS t, COLUMN = embedding,
                   SIMILAR_TO = @q, METRIC = 'cosine') AS r WITH (FORCE_ANN_ONLY)
ORDER BY r.distance;
With no usable index that fails with
Msg 42227
instead of quietly scanning. Use it in a test, not in production.
针对快速相似性搜索请求,通常给出的答案是下面这样。它是错误的,而且不会报告任何错误:
sql
-- Correct results. Never uses a vector index. Computes a distance for EVERY row.
DECLARE @q vector(5) = '[0.3,0.3,0.3,0.3,0.3]';
SELECT TOP (10) id, body, VECTOR_DISTANCE('cosine', embedding, @q) AS distance
FROM dbo.chunks
ORDER BY VECTOR_DISTANCE('cosine', embedding, @q);
使用当前的 DiskANN 索引时,执行计划为
Clustered Index Scan
,随后通过
Compute Scalar
逐行计算
vector_distance
,最后执行
Sort
。索引并未出现在执行计划中。Learn 明确指出:向量距离始终是精确计算,不会使用任何向量索引,即使索引可用。真正能够访问索引的形态是另一种语句,而不是调优选项:
sql
-- Uses the vector index. Vector Index Seek in the plan.
DECLARE @q vector(5) = '[0.3,0.3,0.3,0.3,0.3]';
SELECT TOP (10) WITH APPROXIMATE t.id, t.body, r.distance
FROM VECTOR_SEARCH(TABLE = dbo.chunks AS t, COLUMN = embedding,
                   SIMILAR_TO = @q, METRIC = 'cosine') AS r
WHERE t.category = N'general'
ORDER BY r.distance;
同一张表和同一个索引上的四种形态:
查询执行计划
ORDER BY VECTOR_DISTANCE(...)
搭配
TOP
聚簇索引扫描,精确计算,全扫描
VECTOR_SEARCH
搭配
TOP
,且不使用
WITH APPROXIMATE
聚簇索引扫描,精确 kNN
VECTOR_SEARCH
搭配
TOP (N) WITH APPROXIMATE
向量索引查找,近似计算
在没有向量索引的表上使用
TOP (N) WITH APPROXIMATE
聚簇索引扫描,不会报错
每一种错误形态都会返回正确的结果。 这正是整个风险所在。在几千行数据上测试的流水线能够通过、上线,之后却会在每次查询中永久地为每一行计算一次距离。因此,该缺陷表现为延迟和成本,而不是任何人都能明确指出的失败。Learn 表示在找不到兼容索引时会发出警告;不要指望一定能够看到它。
将静默回退变成失败。
WITH (FORCE_ANN_ONLY)
应放在
VECTOR_SEARCH
别名上,并且要求存在索引和
WITH APPROXIMATE
:
sql
DECLARE @q vector(5) = '[0.3,0.3,0.3,0.3,0.3]';
SELECT TOP (10) WITH APPROXIMATE t.id, r.distance
FROM VECTOR_SEARCH(TABLE = dbo.chunks AS t, COLUMN = embedding,
                   SIMILAR_TO = @q, METRIC = 'cosine') AS r WITH (FORCE_ANN_ONLY)
ORDER BY r.distance;
没有可用索引时,它会报
Msg 42227
,而不是悄悄地执行扫描。可在测试中使用,但不要用于生产环境。

The older syntax most examples still show

大多数示例仍在展示的旧语法

VECTOR_SEARCH(..., TOP_N = 10)
is what most published examples still show. Against a current index it fails with
Msg 42274
. Match on that number, not the text: Learn prints "version 3 index" where the engine prints "newer index version".
TOP_N
belongs to an earlier index format that cannot be upgraded in place, so move the count to
SELECT TOP (N) WITH APPROXIMATE
.
WITH APPROXIMATE
is also not a general modifier. Without a
VECTOR_SEARCH
it fails with
Msg 42248
, and its
ORDER BY
is fixed: the
distance
column, ascending, nothing else, or
Msg 42271
. Learn's own end to end example on the
CREATE VECTOR INDEX
page ends
ORDER BY s.distance, t.title
, which its own
VECTOR_SEARCH
page says is
Msg 42271
. Copy the rule, not the example.
GROUP BY
, window functions,
DISTINCT
, set operators, extra sort keys and
CROSS APPLY
all need the approximate search in a subquery and the outer query doing the rest.
VECTOR_SEARCH(..., TOP_N = 10)
仍是大多数已发布示例所展示的写法。针对当前索引,它会报
Msg 42274
。请根据错误号判断,不要依赖错误文本:Learn 输出的是 "version 3 index",而引擎输出的是 "newer index version"。
TOP_N
属于旧版索引格式,该格式无法原地升级,因此应将数量移到
SELECT TOP (N) WITH APPROXIMATE
。
WITH APPROXIMATE
也不是通用修饰符。如果不配合
VECTOR_SEARCH
使用,它会报
Msg 42248
;其
ORDER BY
也是固定的:必须是
distance
列、升序且不能有其他内容,否则会报
Msg 42271
。Learn 自己在
CREATE VECTOR INDEX
页面上的端到端示例最后使用了
ORDER BY s.distance, t.title
,但其
VECTOR_SEARCH
页面说明这种写法会报
Msg 42271
。请遵循规则,不要照抄示例。
GROUP BY
、窗口函数、
DISTINCT
、集合运算符、额外的排序键以及
CROSS APPLY
都需要将近似搜索放在子查询中,再由外层查询完成其余处理。

What VECTOR_DISTANCE returns, and the sign trap

VECTOR_DISTANCE 的返回值与符号陷阱

Three metrics exist and nothing else does:
'cosine'
,
'euclidean'
,
'dot'
. Anything else is
Msg 42201
. The metric may be a variable and is case insensitive.
sql
DECLARE @q vector(3) = '[1,2,3]';
SELECT v.label,
       VECTOR_DISTANCE('cosine',    CAST(v.txt AS vector(3)), @q) AS cosine,
       VECTOR_DISTANCE('euclidean', CAST(v.txt AS vector(3)), @q) AS euclidean,
       VECTOR_DISTANCE('dot',       CAST(v.txt AS vector(3)), @q) AS dot
FROM (VALUES ('identical', '[1,2,3]'), ('near', '[1,2,4]'), ('far', '[4,5,6]')) AS v(label, txt);
Row
cosine
euclidean
dot
[1,2,3]
, identical
5.96e-080-14
[1,2,4]
0.00851-17
[4,5,6]
0.02545.196-32
It returns a distance: smaller is closer, always, for all three.
  • Cosine returns 1 minus the similarity, on a range Learn gives as 0 to 2. An identical vector scores near zero, so a threshold copied from a similarity based store inverts.
  • dot
    returns the negative inner product.
    The identical row scores -14, not 14. A query that sorts
    dot
    descending, or keeps the largest score, returns the least similar rows, with no error and a full result set, and grounds its answer on the worst matches.
  • Keep both arguments on one base type. Container builds through
    18.0.226
    refuse
    float32
    against
    float16
    with
    Msg 42243
    ;
    18.0.302.0_248
    and
    18.0.310.0_253
    compute a distance, so a mixed comparison runs on some builds and fails on others.
只存在三种指标,除此之外均不支持:
'cosine'
、
'euclidean'
、
'dot'
。使用其他指标会报
Msg 42201
。指标可以是变量,并且不区分大小写。
sql
DECLARE @q vector(3) = '[1,2,3]';
SELECT v.label,
       VECTOR_DISTANCE('cosine',    CAST(v.txt AS vector(3)), @q) AS cosine,
       VECTOR_DISTANCE('euclidean', CAST(v.txt AS vector(3)), @q) AS euclidean,
       VECTOR_DISTANCE('dot',       CAST(v.txt AS vector(3)), @q) AS dot
FROM (VALUES ('identical', '[1,2,3]'), ('near', '[1,2,4]'), ('far', '[4,5,6]')) AS v(label, txt);
行
cosine
euclidean
dot
[1,2,3]
,相同
5.96e-080-14
[1,2,4]
0.00851-17
[4,5,6]
0.02545.196-32
它返回的是距离:对于这三种指标,距离越小始终表示越接近。
  • Cosine 返回 1 减去相似度,Learn 给出的取值范围是 0 到 2。相同向量的得分接近零,因此直接照搬基于相似度的存储系统所使用的阈值会颠倒含义。
  • dot
    返回负的内积。
    相同行的得分是 -14,而不是 14。按
    dot
    降序排序或保留最大得分的查询,会返回最不相似的行;既不会报错,也会返回完整结果集,并据此用最差的匹配项生成答案。
  • 两个参数必须使用相同的基础类型。通过
    18.0.226
    构建的容器会拒绝将
    float32
    与
    float16
    混用,并报
    Msg 42243
    ;
    18.0.302.0_248
    和
    18.0.310.0_253
    则可以计算距离,因此混合比较在某些构建上可以运行,在另一些构建上则会失败。

The type, in one place

集中了解该类型

sql
DECLARE @v vector(3) = '[1,2,3]';
SELECT VECTORPROPERTY(@v, 'Dimensions') AS dims,       -- 3
       VECTORPROPERTY(@v, 'BaseType')   AS base_type,  -- float32
       DATALENGTH(@v)                   AS bytes,      -- 20
       CAST(@v AS nvarchar(max))        AS round_trip;
  • Dimensions are 1 to 1998 at the default
    float32
    , which Learn states and the engine enforces:
    vector(1999)
    is
    Msg 2717
    ,
    vector(0)
    is
    Msg 1001
    . A populated
    vector(1998)
    has
    DATALENGTH
    8000. The declared dimension cannot be changed later, even on an empty table:
    ALTER COLUMN
    is
    Msg 42204
    . A new dimension means a new column. Add it beside the old one, backfill it, move the queries and the vector index to it, and drop the old column last. Dropping the populated column first deletes every stored embedding and leaves search broken until the backfill finishes. A 3072 dimension model does not fit a
    float32
    column: ask the model for 1998 or fewer at embedding time, or declare
    vector(3072, float16)
    .
  • Base type is
    float32
    unless
    float16
    is stated. Learn lists half-precision
    float16
    (up to 3996 dimensions) as generally available on Azure SQL Database with no preview setting; it is preview, behind
    PREVIEW_FEATURES
    , on SQL Server 2025, so check it in the container before relying on it there. Half precision crosses TDS as a JSON string unless the driver supports native
    float16
    transport, and comparing it with a
    float32
    vector depends on the build (see above).
  • It is written and read as a JSON array, converting implicitly from
    varchar
    ,
    nvarchar
    and
    json
    . The dimension in
    CAST
    must be a literal
    :
    CAST(@s AS vector(@d))
    is
    Msg 102
    , so a helper taking the dimension as a parameter needs dynamic SQL.
  • Clients that predate the protocol change see
    nvarchar(max)
    , because Learn records that
    sp_describe_first_result_set
    misreports the type, which is why an ORM maps it to a string. Native handling needs
    Microsoft.Data.SqlClient
    6.1.0 or the JDBC driver 13.1.0.
sql
DECLARE @v vector(3) = '[1,2,3]';
SELECT VECTORPROPERTY(@v, 'Dimensions') AS dims,       -- 3
       VECTORPROPERTY(@v, 'BaseType')   AS base_type,  -- float32
       DATALENGTH(@v)                   AS bytes,      -- 20
       CAST(@v AS nvarchar(max))        AS round_trip;
  • 在默认的
    float32
    下,维度范围为 1 到 1998,Learn 已明确说明这一点,引擎也会强制执行:
    vector(1999)
    会报
    Msg 2717
    ,
    vector(0)
    会报
    Msg 1001
    。包含数据的
    vector(1998)
    的
    DATALENGTH
    为 8000。声明后无法再更改维度,即使表中没有数据也是如此:
    ALTER COLUMN
    会报
    Msg 42204
    。如果需要新维度,就必须使用新列。先在旧列旁添加新列,执行回填,将查询和向量索引迁移到新列,最后再删除旧列。如果先删除包含数据的列,就会删除所有已存储的 embeddings,并导致搜索在回填完成前一直失效。3072 维模型无法放入
    float32
    列:应在生成 embedding 时要求模型输出不超过 1998 维,或者声明
    vector(3072, float16)
    。
  • 基础类型默认为
    float32
    ,除非明确指定
    float16
    。Learn 将半精度
    float16
    (最多支持 3996 维)列为 Azure SQL Database 上的正式发布功能,无需预览设置;在 SQL Server 2025 上,它仍属于需要通过
    PREVIEW_FEATURES
    启用的预览功能,因此在该容器中使用前应先检查。除非驱动程序支持原生
    float16
    传输,否则半精度数据会以 JSON 字符串形式通过 TDS;将其与
    float32
    向量比较是否可用也取决于具体构建版本(见上文)。
  • 该类型以 JSON 数组形式写入和读取,可从
    varchar
    、
    nvarchar
    和
    json
    隐式转换。
    CAST
    中的维度必须是字面量
    :
    CAST(@s AS vector(@d))
    会报
    Msg 102
    ,因此,如果辅助函数需要将维度作为参数传入,就需要使用动态 SQL。
  • 早于协议变更的客户端会将其视为
    nvarchar(max)
    ,因为 Learn 记录了
    sp_describe_first_result_set
    错误报告该类型的问题,这也是 ORM 将其映射为字符串的原因。原生处理需要
    Microsoft.Data.SqlClient
    6.1.0 或 JDBC 驱动 13.1.0。

The restriction list

限制清单

No model infers this: a
vector
column looks like a column.
Refused in a table definition
AttemptError
PRIMARY KEY
or
UNIQUE
on a vector column
Msg 1919
FOREIGN KEY
referencing one
Msg 1776
, no candidate key to reference
CHECK
constraint
Msg 1760
DEFAULT
constraint
Msg 1752
PERSISTED
computed column over a vector function
Msg 4936
, non-deterministic
Partition function or partition key
Msg 7704
Alias type with
CREATE TYPE
Msg 42212
Assignment to
sql_variant
Msg 206
Memory optimized tableNot supported
ALTER COLUMN
to a different dimension
Msg 42204
Allowed, though widely assumed otherwise:
SPARSE
, a non-persisted computed column, an
INCLUDE
column on a nonclustered index, a system versioned temporal table, a table carrying a clustered columnstore index,
SELECT ... INTO
,
ALTER COLUMN
from
nvarchar
to
vector
,
CASE
,
ISNULL
,
UNION ALL
and
FOR JSON
. Also parameters, return types and table variables.
Refused in a query
AttemptError
ORDER BY
or
GROUP BY
the column itself
Msg 42213
, cannot be compared or sorted
SELECT DISTINCT
Msg 421
=
,
<
,
>
,
IN
, a join predicate,
MAX
,
MIN
,
COUNT(DISTINCT ...)
Msg 8117
UNION
,
INTERSECT
,
EXCEPT
Msg 5335
.
UNION ALL
is fine
CREATE STATISTICS
on the column, or any b-tree or columnstore key
Msg 1978
CAST
to
varbinary
Msg 529
Inserting a JSON array of the wrong length
Msg 42204
Inserting a JSON array containing a string
Msg 13670
IS NULL
and
IS NOT NULL
are the only comparisons that work. With no equality, deduplicating rows by their embedding is not a query you can write. Deduplicate on a hash of the source text, which Learn wants anyway: duplicates degrade recall. Always Encrypted is unsupported, and
sp_verify_database_ledger
errors on a database holding a vector column.
模型无法自行推断这些限制,因为
vector
列看起来只是一个普通列。
在表定义中不被允许
尝试错误
在 vector 列上使用
PRIMARY KEY
或
UNIQUE
Msg 1919
引用 vector 列的
FOREIGN KEY
Msg 1776
,没有可引用的候选键
CHECK
约束
Msg 1760
DEFAULT
约束
Msg 1752
  • 基于向量函数的
    PERSISTED
    计算列 |
    Msg 4936
    ,因为它是非确定性的 | | 分区函数或分区键 |
    Msg 7704
    |
  • 使用
    CREATE TYPE
    创建别名类型 |
    Msg 42212
    |
  • 赋值给
    sql_variant
    |
    Msg 206
    | | 内存优化表 | 不受支持 | | 使用
    ALTER COLUMN
    更改为不同维度 |
    Msg 42204
    |
允许使用,尽管很多人误以为不支持:
SPARSE
、非持久化计算列、非聚集索引上的
INCLUDE
列、系统版本化时态表、带有聚簇列存储索引的表、
SELECT ... INTO
、将
ALTER COLUMN
从
nvarchar
改为
vector
、
CASE
、
ISNULL
、
UNION ALL
和
FOR JSON
。参数、返回类型和表变量也可以使用。
在查询中不被允许
尝试错误
对该列本身使用
ORDER BY
或
GROUP BY
Msg 42213
,无法比较或排序
SELECT DISTINCT
Msg 421
=
、
<
、
>
、
IN
、连接谓词、
MAX
、
MIN
、
COUNT(DISTINCT ...)
Msg 8117
UNION
、
INTERSECT
、
EXCEPT
Msg 5335
。
UNION ALL
可用
在该列上执行
CREATE STATISTICS
,或将其用作任何 B-tree 或列存储的键
Msg 1978
CAST
为
varbinary
Msg 529
插入长度错误的 JSON 数组
Msg 42204
插入包含字符串的 JSON 数组
Msg 13670
IS NULL
和
IS NOT NULL
是唯一可用的比较。由于没有相等运算符,无法编写按 embedding 对行进行去重的查询。应根据源文本的哈希值去重,Learn 也希望采用这种方式:重复项会降低召回率。Always Encrypted 不受支持,包含 vector 列的数据库执行
sp_verify_database_ledger
时会报错。

What the index requires, and what it costs

索引的要求与成本

  • The table needs a clustered index. Without one,
    Msg 42254
    . Learn says a primary key clustered index; clustered primary keys on
    int
    ,
    bigint
    ,
    nvarchar
    and a composite
    (int, int)
    all build, on Azure SQL Database too, so
    Msg 42217
    's narrower wording is stale, not the rule. A heap is refused with
    Msg 42254
    , not 42217, and none of these raises
    Msg 42217
    at all
    .
  • At least 100 rows with a non-null vector must already exist, or
    Msg 42266
    . A fresh database has no index until it has content, and every query is silently exact until then. The test table above inserts 150 for headroom.
  • One vector index per column. A second, even with a different metric, is
    Msg 42230
    . The metric is fixed at build time, and asking
    VECTOR_SEARCH
    for a different one falls back silently. Against a
    cosine
    index,
    METRIC = 'euclidean'
    and
    METRIC = 'dot'
    both return a full ordered result set, computed exactly, with no error and no warning. It is not
    Msg 42227
    , and a reader who believes the engine will stop them has no reason to check.
    Msg 42227
    is raised only by
    WITH (FORCE_ANN_ONLY)
    , which is why that hint belongs in the test that proves the index is being read.
  • The Azure SQL Database container builds it too. Validate the production query plan and security behavior against Azure SQL Database before relying on local parity.
OperationResult
TRUNCATE TABLE
Msg 42232
. Drop the index, truncate, reload 100 rows, recreate
ALTER INDEX ... REBUILD
or
DISABLE
Msg 42250
. There is no rebuild
ALTER TABLE ... DROP COLUMN
on the indexed column
Msg 5074
Importing a dacpac or bacpacFails. The export is fine. Drop the indexes before export, recreate after the load
Partitioning the tableNot supported
Learn states the import row: the import creates schema objects before it loads data, so the index is built against an empty table and trips the 100 row minimum.
sqlpackage-import-export
owns the round trip and its sequence; open it before planning either half.
INSERT
,
UPDATE
,
DELETE
and
MERGE
are all supported on a current index and changes are searchable after commit, which is new: earlier index versions made the table read only. Watch
sys.dm_db_vector_indexes
for background maintenance, and drop and recreate the index after replacing most of the embeddings: the graph was built for the old distribution and recall degrades with no error. Filter predicates apply during the search, so a filtered top ten returns ten rows when ten qualify. Give the filter columns a nonclustered index, as the test table above does.
  • 表必须具有聚簇索引。 如果没有,则会报
    Msg 42254
    。Learn 表示要求使用聚集主键索引;在
    int
    、
    bigint
    、
    nvarchar
    和复合
    (int, int)
    上创建的聚集主键均可成功构建,在 Azure SQL Database 上也是如此,因此
    Msg 42217
    中更狭窄的表述已经过时,并非实际规则。堆表会被拒绝并报
    Msg 42254
    ,而不是 42217,并且上述情况均不会触发
    Msg 42217
    。
  • 必须预先存在至少 100 行非 null 向量,否则会报
    Msg 42266
    。全新数据库在拥有数据之前无法建立索引,而在此之前每次查询都会静默执行精确计算。上面的测试表插入了 150 行,以留出余量。
  • 每列只能有一个向量索引。 创建第二个索引时,即使使用不同的指标,也会报
    Msg 42230
    。指标在构建时固定,而让
    VECTOR_SEARCH
    使用不同指标时,会静默回退。对于
    cosine
    索引,
    METRIC = 'euclidean'
    和
    METRIC = 'dot'
    都会返回完整的有序结果集,这些结果通过精确计算得到,既不报错也不发出警告。它不会报
    Msg 42227
    ;如果读者认为引擎会阻止这种操作,就没有理由再进行检查。只有
    WITH (FORCE_ANN_ONLY)
    才会触发
    Msg 42227
    ,因此该提示应放在证明索引确实被读取的测试中。
  • Azure SQL Database 容器也支持构建该索引。 依赖本地环境与云端行为一致之前,应针对 Azure SQL Database 验证生产查询计划和安全行为。
操作结果
TRUNCATE TABLE
Msg 42232
。删除索引,清空表,重新加载 100 行,然后重建索引
ALTER INDEX ... REBUILD
或
DISABLE
Msg 42250
。不支持重建
对已建立索引的列执行
ALTER TABLE ... DROP COLUMN
Msg 5074
导入 dacpac 或 bacpac失败。导出正常。导出前删除索引,加载后重新创建
对表进行分区不受支持
Learn 对导入行为的说明是:导入过程先创建架构对象,再加载数据,因此索引会基于空表构建,并触发至少 100 行的限制。
sqlpackage-import-export
负责整个往返流程及其执行顺序;在规划其中任一环节之前,应先查看它。
当前索引支持
INSERT
、
UPDATE
、
DELETE
和
MERGE
,提交后即可搜索到这些更改,这是新行为:旧版索引会将表设为只读。请通过
sys.dm_db_vector_indexes
监控后台维护,并在替换大多数 embeddings 后删除并重建索引:原图结构是为旧分布构建的,召回率会在没有报错的情况下下降。筛选谓词会在搜索期间应用,因此当有十行满足条件时,筛选后的前十条会返回十行。请像上面的测试表那样,为筛选列创建非聚集索引。

Check it worked

检查是否生效

Three checks. The middle one settles what this skill exists for.
sql
-- 1. The index exists and is the current format. index_version must be 3.
SELECT i.name AS index_name, OBJECT_NAME(v.object_id) AS table_name,
       JSON_VALUE(v.build_parameters, '$.Version') AS index_version
FROM sys.vector_indexes AS v
JOIN sys.indexes AS i ON v.object_id = i.object_id AND v.index_id = i.index_id;

-- 2. The query reaches it. Read the plan and look for Vector Index Seek.
SET STATISTICS XML ON;
DECLARE @q vector(5) = '[0.3,0.3,0.3,0.3,0.3]';
SELECT TOP (10) WITH APPROXIMATE t.id, r.distance
FROM VECTOR_SEARCH(TABLE = dbo.chunks AS t, COLUMN = embedding,
                   SIMILAR_TO = @q, METRIC = 'cosine') AS r
ORDER BY r.distance;
SET STATISTICS XML OFF;

-- 3. Ranking is the right way up: the nearest row has the SMALLEST distance.
DECLARE @p vector(5) = '[0.3,0.3,0.3,0.3,0.3]';
SELECT MIN(VECTOR_DISTANCE('cosine', embedding, @p)) AS nearest,
       MAX(VECTOR_DISTANCE('cosine', embedding, @p)) AS furthest
FROM dbo.chunks;
Run them non-interactively with the password in
SQLCMDPASSWORD
, keeping the output:
bash
sqlcmd -S <server-name>.database.windows.net,1433 -d <database> -U <user> -C \
  -i check-vector-index.sql -o check-vector-index.out
Expected: check 1 returns one row per vector index with
index_version
3
; an empty result set means there is no index and every similarity query is scanning. Check 2's plan contains
Vector Index Seek
; a plan holding only
Clustered Index Scan
and
Compute Scalar
is the defect, whatever the rows look like. Check 3 returns
nearest
below
furthest
, both non-negative.
sqlcmd
exiting 0 says nothing about the three, so read the output file.
执行三项检查。中间一项正是本 skill 存在意义的核心。
sql
-- 1. The index exists and is the current format. index_version must be 3.
SELECT i.name AS index_name, OBJECT_NAME(v.object_id) AS table_name,
       JSON_VALUE(v.build_parameters, '$.Version') AS index_version
FROM sys.vector_indexes AS v
JOIN sys.indexes AS i ON v.object_id = i.object_id AND v.index_id = i.index_id;

-- 2. The query reaches it. Read the plan and look for Vector Index Seek.
SET STATISTICS XML ON;
DECLARE @q vector(5) = '[0.3,0.3,0.3,0.3,0.3]';
SELECT TOP (10) WITH APPROXIMATE t.id, r.distance
FROM VECTOR_SEARCH(TABLE = dbo.chunks AS t, COLUMN = embedding,
                   SIMILAR_TO = @q, METRIC = 'cosine') AS r
ORDER BY r.distance;
SET STATISTICS XML OFF;

-- 3. Ranking is the right way up: the nearest row has the SMALLEST distance.
DECLARE @p vector(5) = '[0.3,0.3,0.3,0.3,0.3]';
SELECT MIN(VECTOR_DISTANCE('cosine', embedding, @p)) AS nearest,
       MAX(VECTOR_DISTANCE('cosine', embedding, @p)) AS furthest
FROM dbo.chunks;
以非交互方式运行它们,将密码放入
SQLCMDPASSWORD
,并保留输出:
bash
sqlcmd -S <server-name>.database.windows.net,1433 -d <database> -U <user> -C \
  -i check-vector-index.sql -o check-vector-index.out
预期结果:检查 1 会为每个向量索引返回一行,其中
index_version
为
3
;如果结果集为空,则说明没有索引,所有相似性查询都在执行扫描。检查 2 的执行计划应包含
Vector Index Seek
;如果计划中只有
Clustered Index Scan
和
Compute Scalar
,无论返回的行看起来是否正确,都说明存在缺陷。检查 3 返回的
nearest
应小于
furthest
,且两者均非负。
sqlcmd
以状态码 0 退出并不代表这三项检查通过,因此必须读取输出文件。

Do not

请勿

  • Do not conclude the index is in use because results look right. Read the plan, or assert with
    FORCE_ANN_ONLY
    .
    WITH APPROXIMATE
    is not a droppable hint: it changes the algorithm.
  • Do not sort a
    dot
    distance descending, and do not present a cosine distance as a similarity.
  • Do not declare
    vector(3072)
    at the default
    float32
    : the ceiling is 1998. Ask the model for fewer dimensions, or declare
    vector(3072, float16)
    and accept half precision.
  • Do not deduplicate on the embedding. There is no equality operator to do it with.
  • Do not raise the compatibility level or set
    PREVIEW_FEATURES = ON
    . Neither is the gate, and the container's
    1
    is not the cloud's value.
  • Do not report that the index cannot be built in the container. It builds there.
  • Do not build the ingest, chunking and grounding pipeline here. That is
    rag-on-azure-sql
    .
  • 不要因为结果看起来正确,就断定索引正在被使用。应查看执行计划,或使用
    FORCE_ANN_ONLY
    进行断言。
    WITH APPROXIMATE
    不是可以随意移除的提示:它会改变算法。
  • 不要对
    dot
    距离进行降序排序,也不要把 cosine 距离当作相似度。
  • 不要在默认
    float32
    下声明
    vector(3072)
    :上限是 1998。应要求模型输出更少的维度,或者声明
    vector(3072, float16)
    并接受半精度。
  • 不要按 embedding 去重。该类型没有可用于此目的的相等运算符。
  • 不要提高兼容级别,也不要设置
    PREVIEW_FEATURES = ON
    。两者都不是门槛,而且容器中的
    1
    不代表云端的值。
  • 不要声称容器中无法构建该索引。它可以在容器中构建。
  • 不要在这里构建 ingest、chunking 和 grounding 流水线;这些属于
    rag-on-azure-sql
    。

References

参考资料

  • references/vector-restrictions-and-query-plans.md when a claim here disagrees with what you are seeing, which can happen as engine builds change: it holds the raw statements behind every table above, the four plans, and checks you can run.
  • Read vector data type before choosing a dimension count or a driver, and CREATE VECTOR INDEX before creating an index in production, for availability status and the migration off the earlier format.
  • Read VECTOR_SEARCH when a query will not compile alongside
    WITH APPROXIMATE
    , for the subquery patterns, and Feature availability by region before promising the index in a region you have not built one in.
  • 如果本文中的说法与实际观察结果不一致,请参阅 references/vector-restrictions-and-query-plans.md。随着引擎构建版本发生变化,这种情况确实可能发生。该文件保存了上述每张表背后的原始语句、四种执行计划以及可运行的检查。
  • 在选择维度数量或驱动程序之前,请阅读 vector data type;在生产环境创建索引之前,请阅读 CREATE VECTOR INDEX,以了解可用状态以及从旧格式迁移的方法。
  • 当查询无法与
    WITH APPROXIMATE
    一起编译时,请阅读 VECTOR_SEARCH 以了解子查询模式;在承诺某个区域支持该索引之前,请阅读 Feature availability by region。