SQL Server无原生向量类型时如何高性能创建向量索引

针对256维NLP向量的存储、余弦相似度计算、检索性能问题，以下方案比逐维度建列的实现更易维护、性能更高：

优先推荐：VARBINARY二进制存储+分桶预筛选+批量计算 #

这是目前生产环境用SQL Server做向量检索性价比最高的方案，没有额外组件依赖，和你现有的ML Services（Python）栈适配度极高：

• 存储层：将256个32位浮点型向量值按固定顺序序列化为二进制，存入VARBINARY(1024)类型列即可——单条向量固定占1024字节，没有冗余存储开销，后续如果调整向量维度只需要改字段长度定义，不需要新增/删除几百个维度列。
• 检索优化：不要直接全表扫描计算余弦相似度，提前给每条向量计算1_{2个LSH（局部敏感哈希）分桶值，存入TINYINT/SMALLINT类型的列，给该列建普通非聚集索引。查询时先计算查询向量对应的分桶值，通过索引筛掉90%以上完全不相关的行，只对筛选出的小候选集做精确余弦计算，检索速度可以提升10}100倍。
• 计算层：不要逐行调用Python脚本计算相似度，借助sp_execute_external_script把候选集的二进制向量批量传给Python运行时，用numpy做矩阵化的余弦相似度计算，避免跨进程逐行调用的开销。基础实现模板如下：

EXEC sp_execute_external_script
    @language = N'Python',
    @script = N'
import numpy as np
import json
# 读取传入的查询向量
query_vec = np.array(json.loads(query_vec_str), dtype=np.float32).reshape(1, -1)
# 批量反序列化二进制列为向量矩阵
vec_matrix = np.frombuffer(
    b"".join(InputDataSet["vec_bin"].to_list()), 
    dtype=np.float32
).reshape(-1, 256)
# 批量计算余弦相似度
vec_norm = np.linalg.norm(vec_matrix, axis=1, keepdims=True)
query_norm = np.linalg.norm(query_vec)
similarity = (vec_matrix @ query_vec.T).flatten() / (vec_norm.flatten() * query_norm)
# 拼接结果返回Top20最相似内容
res = InputDataSet[["row_id", "text_content"]].copy()
res["cosine_similarity"] = similarity
OutputDataSet = res.sort_values("cosine_similarity", ascending=False).head(20)
    ',
    @input_data_1 = N'SELECT row_id, text_content, vec_bin FROM biz_table WHERE lsh_bucket_id = @calc_bucket',
    @params = N'@query_vec_str NVARCHAR(MAX), @calc_bucket SMALLINT',
    @query_vec_str = @query_vec_json,
    @calc_bucket = @query_lsh_bucket
WITH RESULT SETS ((row_id BIGINT, text_content NVARCHAR(MAX), cosine_similarity FLOAT));

小数据量可选：JSON数组存储 #

如果你的表数据量在10万行以内、后续向量维度可能频繁调整，可以用NVARCHAR(MAX)存标准JSON格式的向量数组，比如[0.12, 0.34, ..., 0.78]，借助SQL Server原生的OPENJSON函数就能解析成数值集合做计算。缺点是JSON解析和存储开销比二进制方案高2~3倍，数据量大了之后性能衰减明显，不推荐百万级以上数据量使用。

不推荐方案说明 #

• 逐维度单独建列：表结构维护成本极高，换模型调整维度时需要批量增删上百个字段，且逐列取值计算的执行计划冗余度高，数据量上来后性能远低于二进制存储方案。
• 逗号分隔字符串存向量：字符串拆分、类型转换的开销是所有方案里最高的，没有任何性能优势，完全不建议使用。
• 全文搜索：如你所说，全文搜索的文本预处理逻辑不可控，对非规整文本的语义匹配效果远不如自定义NLP流程生成向量的方案，确实不适用你的场景。

内容的提问来源于stack exchange，提问作者fnjo