vector-search-azure-sql
Compare original and translation side by side
🇺🇸
Original
English🇨🇳
Translation
ChineseVector storage and search on Azure SQL Database
Azure SQL Database 上的向量存储与搜索
The 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.
vectorUnless 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
5.
SERVERPROPERTY('EngineEdition')在此引擎中, 类型的行为与其他任何列类型都不同;看起来像向量搜索的查询,并不是会使用向量索引的查询。
vector除非某一行明确指出具体构建版本,否则以下所有错误号和执行计划在兼容级别 170 的 Azure SQL Database 和 Azure SQL Database 容器上均相同;二者报告的 都是 5。
SERVERPROPERTY('EngineEdition')Scope
适用范围
- Owns the type and the query surface on Azure SQL Database.
vector - The end to end pipeline is , generating embeddings is
rag-on-azure-sql, and a vector column's place in a wider design isembeddings-and-external-models.design-azure-sql-schema
- 涵盖 Azure SQL Database 上的 类型及查询接口。
vector - 端到端流水线是 ,生成 embeddings 是
rag-on-azure-sql,而 vector 列在更广泛设计中的位置属于embeddings-and-external-models。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, and
approximate search over an existing index all work at level 150. is not the
gate either:
VECTOR_DISTANCEPREVIEW_FEATURESsql
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
and both read 0. Learn documents OFF as the default, scopes the requirement to the boxed
engine, and its own 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, , , and are
generally available. On Azure SQL Database , and
are generally available too; they are preview only on SQL Server 2025,
behind . Check feature availability by region before a design depends on the index.
modelmasterCREATE VECTOR INDEXVECTOR_DISTANCEVECTOR_NORMVECTOR_NORMALIZEVECTORPROPERTYCREATE VECTOR INDEXVECTOR_SEARCHTOP (N) WITH APPROXIMATEPREVIEW_FEATURES不要只阅读索引文档。先创建一个。这是了解引擎索引行为最快的方法。
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、 以及基于现有索引的近似搜索在级别 150 下均可使用。 也不是门槛:
VECTOR_DISTANCEPREVIEW_FEATURESsql
SELECT [name], [value] FROM sys.database_scoped_configurations WHERE [name] = 'PREVIEW_FEATURES';不要仅凭该数值下结论。 在容器中,该值并不确定:同一引擎上新创建的数据库有时返回 0,有时返回 1,不同数据库之间也存在差异,而 和 均返回 0。Learn 文档将 OFF 记为默认值,并将这一要求限定在框出的引擎范围内;其自身的 示例称该设置为 "not needed for Azure SQL Database"。真正重要的是上面的语句:索引可以成功构建,而且你无须设置任何内容。如果某个文档告诉你必须先启用它,那么无论你的数据库恰好报告什么,该文档都是错误的。 类型、、、 和 均已正式发布。在 Azure SQL Database 上,、 和 也已正式发布;只有在 SQL Server 2025 中,它们才需要通过 启用预览功能。在设计依赖索引之前,请检查各区域的可用功能。
modelmasterCREATE VECTOR INDEXvectorVECTOR_DISTANCEVECTOR_NORMVECTOR_NORMALIZEVECTORPROPERTYCREATE VECTOR_INDEXVECTOR_SEARCHTOP (N) WITH APPROXIMATEPREVIEW_FEATURESThe 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 , then
evaluating per row, then . 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:
Clustered Index ScanCompute Scalarvector_distanceSortsql
-- 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:
| Query | Plan |
|---|---|
| Clustered Index Scan, exact, full scan |
| Clustered Index Scan, exact kNN |
| Vector Index Seek, approximate |
| 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. goes on the alias
and needs an index and present:
WITH (FORCE_ANN_ONLY)VECTOR_SEARCHWITH APPROXIMATEsql
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 instead of quietly scanning. Use it in a test, not
in production.
Msg 42227针对快速相似性搜索请求,通常给出的答案是下面这样。它是错误的,而且不会报告任何错误:
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 索引时,执行计划为 ,随后通过 逐行计算 ,最后执行 。索引并未出现在执行计划中。Learn 明确指出:向量距离始终是精确计算,不会使用任何向量索引,即使索引可用。真正能够访问索引的形态是另一种语句,而不是调优选项:
Clustered Index ScanCompute Scalarvector_distanceSortsql
-- 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;同一张表和同一个索引上的四种形态:
| 查询 | 执行计划 |
|---|---|
| 聚簇索引扫描,精确计算,全扫描 |
| 聚簇索引扫描,精确 kNN |
| 向量索引查找,近似计算 |
在没有向量索引的表上使用 | 聚簇索引扫描,不会报错 |
每一种错误形态都会返回正确的结果。 这正是整个风险所在。在几千行数据上测试的流水线能够通过、上线,之后却会在每次查询中永久地为每一行计算一次距离。因此,该缺陷表现为延迟和成本,而不是任何人都能明确指出的失败。Learn 表示在找不到兼容索引时会发出警告;不要指望一定能够看到它。
将静默回退变成失败。 应放在 别名上,并且要求存在索引和 :
WITH (FORCE_ANN_ONLY)VECTOR_SEARCHWITH APPROXIMATEsql
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 42227The older syntax most examples still show
大多数示例仍在展示的旧语法
VECTOR_SEARCH(..., TOP_N = 10)Msg 42274TOP_NSELECT TOP (N) WITH APPROXIMATEWITH APPROXIMATEVECTOR_SEARCHMsg 42248ORDER BYdistanceMsg 42271CREATE VECTOR INDEXORDER BY s.distance, t.titleVECTOR_SEARCHMsg 42271GROUP BYDISTINCTCROSS APPLYVECTOR_SEARCH(..., TOP_N = 10)Msg 42274TOP_NSELECT TOP (N) WITH APPROXIMATEWITH APPROXIMATEVECTOR_SEARCHMsg 42248ORDER BYdistanceMsg 42271CREATE VECTOR INDEXORDER BY s.distance, t.titleVECTOR_SEARCHMsg 42271GROUP BYDISTINCTCROSS APPLYWhat VECTOR_DISTANCE returns, and the sign trap
VECTOR_DISTANCE 的返回值与符号陷阱
Three metrics exist and nothing else does: , , . Anything else is
. The metric may be a variable and is case insensitive.
'cosine''euclidean''dot'Msg 42201sql
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 | | | |
|---|---|---|---|
| 5.96e-08 | 0 | -14 |
| 0.0085 | 1 | -17 |
| 0.0254 | 5.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.
- returns the negative inner product. The identical row scores -14, not 14. A query that sorts
dotdescending, 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.dot - Keep both arguments on one base type. Container builds through refuse
18.0.226againstfloat32withfloat16;Msg 42243and18.0.302.0_248compute a distance, so a mixed comparison runs on some builds and fails on others.18.0.310.0_253
只存在三种指标,除此之外均不支持:、、。使用其他指标会报 。指标可以是变量,并且不区分大小写。
'cosine''euclidean''dot'Msg 42201sql
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);| 行 | | | |
|---|---|---|---|
| 5.96e-08 | 0 | -14 |
| 0.0085 | 1 | -17 |
| 0.0254 | 5.196 | -32 |
它返回的是距离:对于这三种指标,距离越小始终表示越接近。
- Cosine 返回 1 减去相似度,Learn 给出的取值范围是 0 到 2。相同向量的得分接近零,因此直接照搬基于相似度的存储系统所使用的阈值会颠倒含义。
- 返回负的内积。 相同行的得分是 -14,而不是 14。按
dot降序排序或保留最大得分的查询,会返回最不相似的行;既不会报错,也会返回完整结果集,并据此用最差的匹配项生成答案。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 , which Learn states and the engine enforces:
float32isvector(1999),Msg 2717isvector(0). A populatedMsg 1001hasvector(1998)8000. The declared dimension cannot be changed later, even on an empty table:DATALENGTHisALTER COLUMN. 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 aMsg 42204column: ask the model for 1998 or fewer at embedding time, or declarefloat32.vector(3072, float16) - Base type is unless
float32is stated. Learn lists half-precisionfloat16(up to 3996 dimensions) as generally available on Azure SQL Database with no preview setting; it is preview, behindfloat16, 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 nativePREVIEW_FEATUREStransport, and comparing it with afloat16vector depends on the build (see above).float32 - It is written and read as a JSON array, converting implicitly from ,
varcharandnvarchar. The dimension injsonmust be a literal:CASTisCAST(@s AS vector(@d)), so a helper taking the dimension as a parameter needs dynamic SQL.Msg 102 - Clients that predate the protocol change see , because Learn records that
nvarchar(max)misreports the type, which is why an ORM maps it to a string. Native handling needssp_describe_first_result_set6.1.0 or the JDBC driver 13.1.0.Microsoft.Data.SqlClient
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;- 在默认的 下,维度范围为 1 到 1998,Learn 已明确说明这一点,引擎也会强制执行:
float32会报vector(1999),Msg 2717会报vector(0)。包含数据的Msg 1001的vector(1998)为 8000。声明后无法再更改维度,即使表中没有数据也是如此:DATALENGTH会报ALTER COLUMN。如果需要新维度,就必须使用新列。先在旧列旁添加新列,执行回填,将查询和向量索引迁移到新列,最后再删除旧列。如果先删除包含数据的列,就会删除所有已存储的 embeddings,并导致搜索在回填完成前一直失效。3072 维模型无法放入Msg 42204列:应在生成 embedding 时要求模型输出不超过 1998 维,或者声明float32。vector(3072, float16) - 基础类型默认为 ,除非明确指定
float32。Learn 将半精度float16(最多支持 3996 维)列为 Azure SQL Database 上的正式发布功能,无需预览设置;在 SQL Server 2025 上,它仍属于需要通过float16启用的预览功能,因此在该容器中使用前应先检查。除非驱动程序支持原生PREVIEW_FEATURES传输,否则半精度数据会以 JSON 字符串形式通过 TDS;将其与float16向量比较是否可用也取决于具体构建版本(见上文)。float32 - 该类型以 JSON 数组形式写入和读取,可从 、
varchar和nvarchar隐式转换。json中的维度必须是字面量:CAST会报CAST(@s AS vector(@d)),因此,如果辅助函数需要将维度作为参数传入,就需要使用动态 SQL。Msg 102 - 早于协议变更的客户端会将其视为 ,因为 Learn 记录了
nvarchar(max)错误报告该类型的问题,这也是 ORM 将其映射为字符串的原因。原生处理需要sp_describe_first_result_set6.1.0 或 JDBC 驱动 13.1.0。Microsoft.Data.SqlClient
The restriction list
限制清单
No model infers this: a column looks like a column.
vectorRefused in a table definition
| Attempt | Error |
|---|---|
| |
| |
| |
| |
| |
| Partition function or partition key | |
Alias type with | |
Assignment to | |
| Memory optimized table | Not supported |
| |
Allowed, though widely assumed otherwise: , a non-persisted
computed column, an column on a nonclustered index, a system versioned temporal table, a
table carrying a clustered columnstore index, , from to
, , , and . Also parameters, return types and table
variables.
SPARSEINCLUDESELECT ... INTOALTER COLUMNnvarcharvectorCASEISNULLUNION ALLFOR JSONRefused in a query
| Attempt | Error |
|---|---|
| |
| |
| |
| |
| |
| |
| Inserting a JSON array of the wrong length | |
| Inserting a JSON array containing a string | |
IS NULLIS NOT NULLsp_verify_database_ledger模型无法自行推断这些限制,因为 列看起来只是一个普通列。
vector在表定义中不被允许
| 尝试 | 错误 |
|---|---|
在 vector 列上使用 | |
引用 vector 列的 | |
| |
| |
- 基于向量函数的 计算列 |
PERSISTED,因为它是非确定性的 | | 分区函数或分区键 |Msg 4936|Msg 7704 - 使用 创建别名类型 |
CREATE TYPE|Msg 42212 - 赋值给 |
sql_variant| | 内存优化表 | 不受支持 | | 使用Msg 206更改为不同维度 |ALTER COLUMN|Msg 42204
允许使用,尽管很多人误以为不支持: 、非持久化计算列、非聚集索引上的 列、系统版本化时态表、带有聚簇列存储索引的表、、将 从 改为 、、、 和 。参数、返回类型和表变量也可以使用。
SPARSEINCLUDESELECT ... INTOALTER COLUMNnvarcharvectorCASEISNULLUNION ALLFOR JSON在查询中不被允许
| 尝试 | 错误 |
|---|---|
对该列本身使用 | |
| |
| |
| |
在该列上执行 | |
| |
| 插入长度错误的 JSON 数组 | |
| 插入包含字符串的 JSON 数组 | |
IS NULLIS NOT NULLsp_verify_database_ledgerWhat the index requires, and what it costs
索引的要求与成本
- The table needs a clustered index. Without one, . Learn says a primary key clustered index; clustered primary keys on
Msg 42254,int,bigintand a compositenvarcharall build, on Azure SQL Database too, so(int, int)'s narrower wording is stale, not the rule. A heap is refused withMsg 42217, not 42217, and none of these raisesMsg 42254at all.Msg 42217 - At least 100 rows with a non-null vector must already exist, or . 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.
Msg 42266 - One vector index per column. A second, even with a different metric, is . The metric is fixed at build time, and asking
Msg 42230for a different one falls back silently. Against aVECTOR_SEARCHindex,cosineandMETRIC = 'euclidean'both return a full ordered result set, computed exactly, with no error and no warning. It is notMETRIC = 'dot', and a reader who believes the engine will stop them has no reason to check.Msg 42227is raised only byMsg 42227, which is why that hint belongs in the test that proves the index is being read.WITH (FORCE_ANN_ONLY) - 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.
| Operation | Result |
|---|---|
| |
| |
| |
| Importing a dacpac or bacpac | Fails. The export is fine. Drop the indexes before export, recreate after the load |
| Partitioning the table | Not 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. owns the
round trip and its sequence; open it before planning either half.
sqlpackage-import-exportINSERTUPDATEDELETEMERGEsys.dm_db_vector_indexes- 表必须具有聚簇索引。 如果没有,则会报 。Learn 表示要求使用聚集主键索引;在
Msg 42254、int、bigint和复合nvarchar上创建的聚集主键均可成功构建,在 Azure SQL Database 上也是如此,因此(int, int)中更狭窄的表述已经过时,并非实际规则。堆表会被拒绝并报Msg 42217,而不是 42217,并且上述情况均不会触发Msg 42254。Msg 42217 - 必须预先存在至少 100 行非 null 向量,否则会报 。全新数据库在拥有数据之前无法建立索引,而在此之前每次查询都会静默执行精确计算。上面的测试表插入了 150 行,以留出余量。
Msg 42266 - 每列只能有一个向量索引。 创建第二个索引时,即使使用不同的指标,也会报 。指标在构建时固定,而让
Msg 42230使用不同指标时,会静默回退。对于VECTOR_SEARCH索引,cosine和METRIC = 'euclidean'都会返回完整的有序结果集,这些结果通过精确计算得到,既不报错也不发出警告。它不会报METRIC = 'dot';如果读者认为引擎会阻止这种操作,就没有理由再进行检查。只有Msg 42227才会触发WITH (FORCE_ANN_ONLY),因此该提示应放在证明索引确实被读取的测试中。Msg 42227 - Azure SQL Database 容器也支持构建该索引。 依赖本地环境与云端行为一致之前,应针对 Azure SQL Database 验证生产查询计划和安全行为。
| 操作 | 结果 |
|---|---|
| |
| |
对已建立索引的列执行 | |
| 导入 dacpac 或 bacpac | 失败。导出正常。导出前删除索引,加载后重新创建 |
| 对表进行分区 | 不受支持 |
Learn 对导入行为的说明是:导入过程先创建架构对象,再加载数据,因此索引会基于空表构建,并触发至少 100 行的限制。 负责整个往返流程及其执行顺序;在规划其中任一环节之前,应先查看它。
sqlpackage-import-export当前索引支持 、、 和 ,提交后即可搜索到这些更改,这是新行为:旧版索引会将表设为只读。请通过 监控后台维护,并在替换大多数 embeddings 后删除并重建索引:原图结构是为旧分布构建的,召回率会在没有报错的情况下下降。筛选谓词会在搜索期间应用,因此当有十行满足条件时,筛选后的前十条会返回十行。请像上面的测试表那样,为筛选列创建非聚集索引。
INSERTUPDATEDELETEMERGEsys.dm_db_vector_indexesCheck 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 , keeping the output:
SQLCMDPASSWORDbash
sqlcmd -S <server-name>.database.windows.net,1433 -d <database> -U <user> -C \
-i check-vector-index.sql -o check-vector-index.outExpected: check 1 returns one row per vector index with ; an empty result set
means there is no index and every similarity query is scanning. Check 2's plan contains
; a plan holding only and is the defect,
whatever the rows look like. Check 3 returns below , both non-negative.
exiting 0 says nothing about the three, so read the output file.
index_version3Vector Index SeekClustered Index ScanCompute Scalarnearestfurthestsqlcmd执行三项检查。中间一项正是本 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;以非交互方式运行它们,将密码放入 ,并保留输出:
SQLCMDPASSWORDbash
sqlcmd -S <server-name>.database.windows.net,1433 -d <database> -U <user> -C \
-i check-vector-index.sql -o check-vector-index.out预期结果:检查 1 会为每个向量索引返回一行,其中 为 ;如果结果集为空,则说明没有索引,所有相似性查询都在执行扫描。检查 2 的执行计划应包含 ;如果计划中只有 和 ,无论返回的行看起来是否正确,都说明存在缺陷。检查 3 返回的 应小于 ,且两者均非负。 以状态码 0 退出并不代表这三项检查通过,因此必须读取输出文件。
index_version3Vector Index SeekClustered Index ScanCompute ScalarnearestfurthestsqlcmdDo not
请勿
- Do not conclude the index is in use because results look right. Read the plan, or assert with
.
FORCE_ANN_ONLYis not a droppable hint: it changes the algorithm.WITH APPROXIMATE - Do not sort a distance descending, and do not present a cosine distance as a similarity.
dot - Do not declare at the default
vector(3072): the ceiling is 1998. Ask the model for fewer dimensions, or declarefloat32and accept half precision.vector(3072, float16) - Do not deduplicate on the embedding. There is no equality operator to do it with.
- Do not raise the compatibility level or set . Neither is the gate, and the container's
PREVIEW_FEATURES = ONis not the cloud's value.1 - 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 - 不要对 距离进行降序排序,也不要把 cosine 距离当作相似度。
dot - 不要在默认 下声明
float32:上限是 1998。应要求模型输出更少的维度,或者声明vector(3072)并接受半精度。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 , for the subquery patterns, and Feature availability by region before promising the index in a region you have not built one in.
WITH APPROXIMATE
- 如果本文中的说法与实际观察结果不一致,请参阅 references/vector-restrictions-and-query-plans.md。随着引擎构建版本发生变化,这种情况确实可能发生。该文件保存了上述每张表背后的原始语句、四种执行计划以及可运行的检查。
- 在选择维度数量或驱动程序之前,请阅读 vector data type;在生产环境创建索引之前,请阅读 CREATE VECTOR INDEX,以了解可用状态以及从旧格式迁移的方法。
- 当查询无法与 一起编译时,请阅读 VECTOR_SEARCH 以了解子查询模式;在承诺某个区域支持该索引之前,请阅读 Feature availability by region。
WITH APPROXIMATE