SQL Server过滤唯一索引：空值可重复、非空值唯一的完美方案

说实话，这类“允许重复空值、禁止重复非空值”的需求，我在实际业务里碰上过不少次，尤其是做用户体系、订单编号、扩展属性这类表的时候。很多开发者第一反应是加个唯一索引，结果发现 NULL 值也被算进去了，要么直接报错建不上，要么就是达不到“多个空值共存”的效果。今天就把这个问题的来龙去脉、几种可行方案、实际踩坑记录一次性聊透。

先说结论：直接用 SQL Server 的过滤唯一索引（Filtered Unique Index）就能漂亮解决，语法简单、性能好、代码维护成本低，但前提是你得先理解 SQL Server 对 NULL 的唯一性处理机制，以及过滤索引的几个隐藏脾气。否则后续用起来，还是会遇到各种匪夷所思的坑。

1. 先看需求本质：这个约束到底在约束什么

1.1 空值与非空值的不同语义

这类需求最典型的场景，就是“可选唯一编码”字段。举个例子：一张用户表里有个“邀请码”，用户没申请时字段是 NULL，申请之后填一个字符串，这个字符串在全表范围内不能重复。换成人话就是：

• 允许 100 个用户的邀请码都是 NULL，互不干扰；
• 一旦某个用户填了 “ABC123”，另一个用户就不能也填 “ABC123”；
• 空字符串和 NULL 是两码事，如果你用空字符串来代表“未填写”，那唯一索引就会把空字符串当成普通值处理，多个空字符串就会冲突。

这个看起来简单的规则，如果用普通唯一索引实现，你会发现 SQL Server 的行为跟你想的完全不一样。

1.2 SQL Server 在 NULL 上天生不同

在 SQL Server 里，唯一索引对 NULL 的处理逻辑，和大多数人的直觉相反。它把每个 NULL 都当成“互不相同的值”，所以同一列上你可以插入无数个 NULL，而不会触发唯一键冲突。

这个特性其实是很多开发者以为“SQL Server 不支持空值唯一约束”的误区来源。很多人想问“不为空时唯一”怎么办，其实 SQL Server 默认就能让空值重复。真正麻烦的是得让非空值也唯一，同时保证索引能正常工作。

举个例子，一张表有两行：第一行某列是 NULL，第二行该列也是 NULL。加上普通唯一索引后，这两行能共存。但如果你再加一行，该列是空字符串 ''，那就会跟下一个非空值一样，遵守唯一约束的规则。所以说，NULL 和空字符串在唯一约束里的待遇完全不同。

1.3 为什么不能直接用 UNIQUE 约束一把梭

很多人会问，那是不是加一个 UNIQUE 约束就够了？答案是不行。

UNIQUE 约束在 SQL Server 里本质上也是创建一个唯一索引，它的 NULL 行为跟上面说的普通唯一索引完全一致。也就是说，光是加一个 UNIQUE 约束，确实能让“非空值唯一”，也能让“多个 NULL 共存”，看似需求已经满足。

但问题出在约束的语义和后续维护上。UNIQUE 约束是全列约束，它不能表达“我只关心非 NULL 部分”这种过滤逻辑。一旦未来需求演变成“删除标记为 1 时唯一，删除标记为 0 时无所谓”，UNIQUE 约束就彻底无能为力了。而过滤唯一索引可以通过筛选条件轻松扩展。

另外，UNIQUE 约束没办法做条件覆盖，比如你希望“只有状态为正常时，编号才唯一；状态异常时，编号随便填”这种复合条件，UNIQUE 约束只能约束“任意状态下的编号都不重复”，这就矫枉过正了。

所以，用 UNIQUE 约束虽然能“歪打正着”满足简单场景，但不是最优解。真正可扩展、语义清晰、推荐的做法是过滤唯一索引。

2. 推荐方案：过滤唯一索引

2.1 建索引的完整 SQL

过滤唯一索引，说白了就是在唯一索引的创建语句后面加一个 WHERE 条件，让索引只包含满足条件的行。这样，不满足条件的行（比如 NULL）就可以重复存在，而满足条件的行（非 NULL 值）会被唯一约束管着。

给一段可以直接抄走的建表 SQL：

CREATE TABLE dbo.Users ( UserId INT IDENTITY PRIMARY KEY, UserName NVARCHAR(50) NOT NULL, InviteCode NVARCHAR(20) NULL ); -- 核心语句：过滤唯一索引 CREATE UNIQUE NONCLUSTERED INDEX UQ_Users_InviteCode ON dbo.Users(InviteCode) WHERE InviteCode IS NOT NULL;

你只需要把InviteCode替换成自己的字段名，把UQ_Users_InviteCode换成合适的索引名即可。

这个索引的语义非常直白：只有InviteCode IS NOT NULL的行才会进入索引，而每一行进去都要求InviteCode值唯一。NULL 的行压根不参与，自然也不会互相冲突。

2.2 索引设计背后的三个细节

细节一：筛选条件必须和查询条件一致。过滤索引的 WHERE 条件本身就是一个“索引提示”，只有当你的查询 SQL 里同样带有WHERE InviteCode IS NOT NULL时，优化器才会更容易选到这个索引。否则，查询可能走全表扫描，索引存在感为零。虽然对更新来说，约束仍然生效，但查询性能会打折扣。

细节二：过滤索引的统计信息是独立的。SQL Server 会为过滤索引单独生成统计信息，这意味着它的直方图、密度信息只基于过滤后的数据集合。如果你的表中 NULL 占比极大，过滤索引的统计信息会和全表统计信息差异很大，优化器选择的时候会做出不同判断。这个特性既可以是优势（更精准），也可能是坑（误判选择性）。

细节三：过滤索引会增加少量的写开销。每次插入或更新操作，SQL Server 都要额外判断该行是否满足过滤条件，满足则维护索引，不满足则跳过。这个判断非常轻微，但当表极其庞大且写入吞吐量极高时，仍然值得关注，过度滥用过滤索引会把表变成“索引博物馆”，写入性能会明显下降。

2.3 这个方案最适合的场景

过滤唯一索引不只是能解决“空值可重复、非空值唯一”这一种需求，它更像一把瑞士军刀。

场景一：软删除唯一约束。业务上经常遇到“逻辑删除后，允许重新创建同名的东西”。比如某个配置表里，记录 A 被删除了（DeletedFlag = 1），你再新建一个和 A 同名的记录，应该允许。这时候普通唯一索引会拦截，而过滤唯一索引可以这样写：

CREATE UNIQUE NONCLUSTERED INDEX UQ_Config_Name_Active ON dbo.Config (Name) WHERE DeletedFlag = 0;

场景二：按类型分区唯一。同一个表存储多种类型的业务单据，每种单据有自己的单号规则，单号只要求在同类型内唯一。这时候过滤条件写成WHERE BizType = 'Order'，再写一个WHERE BizType = 'Invoice'，互不干扰。

场景三：状态机字段。只让状态为“启用”的记录编码唯一，其他状态随便填。一条过滤索引就搞定。

正因为过滤索引能处理这类复杂语义，我个人强烈建议：凡是被 UNIQUE 约束别扭地限制住的场景，都该优先考虑过滤唯一索引。

3. 备选方案：触发器与生成列

虽然过滤唯一索引是首选，但有些老系统环境不支持过滤索引（比如 SQL Server 2005 及更早版本），或者 DBA 不允许新增索引，这时候你可能就得退而求其次，用触发器或生成列来完成约束。虽然它们不是最优解，但确实能干活，值得了解。

3.1 触发器方案的完整实现

思路很朴素：在插入和更新后，检查非 NULL 列是否存在重复值，如果有则回滚事务。

CREATE TRIGGER TRG_Users_UniqueInviteCode ON dbo.Users AFTER INSERT, UPDATE AS BEGIN SET NOCOUNT ON; -- 只要插入或更新的行里存在非空编码，就检查全表是否有重复 IF EXISTS ( SELECT 1 FROM dbo.Users u INNER JOIN ( SELECT InviteCode FROM dbo.Users WHERE InviteCode IS NOT NULL GROUP BY InviteCode HAVING COUNT(*) > 1 ) dup ON u.InviteCode = dup.InviteCode ) BEGIN RAISERROR('InviteCode 非空值必须唯一', 16, 1); ROLLBACK TRANSACTION; RETURN; END END;

这段触发器能保证业务层面的一致性：任何非空重复的 InviteCode 插入，都会导致整个事务回滚。

不过，它有个明显问题：如果原表里已经有重复数据，触发器会在每次 DML 操作后扫描一遍，即便你不是针对重复行操作，也会被拦截。换句话说，你得确保存量数据是干净的。

另外，这个触发器用了AFTER，意味着操作已经写进日志，再回滚，性能上会有额外开销。如果是高频插入场景，触发器方案会很吃力。

3.2 触发器方案的坑

第一个坑：多行插入性能。一次 INSERT 上万行时，触发器里扫描全表重复组的开销会很大，而且如果中间有一行重复，整个事务回滚，应用端看到的就是一个长超时错误。

第二个坑：与约束的执行顺序。AFTER 触发器的检查时机是在所有约束都检查完之后，所以你的业务逻辑里如果有别的约束先报错，触发器根本不会执行。这本来是好事，但如果你依赖触发器来做唯一校验，别人可能会误以为“已经过了唯一校验”，从而忽略数据质量问题。

第三个坑：调试困难。触发器是隐式执行的，应用开发者通常意识不到它的存在。代码里插入数据时看到报错，第一反应是 SQL 写错了，查半天才发现是触发器在拦路。

既然有更干净的方案，触发器我建议只做兜底，或者干脆不做。不过有时候确实没法用过滤索引，比如老的 SQL Server 版本，那就只能用心维护触发器逻辑。

3.3 生成列方案：老版本的兼容招

生成列方案利用一个额外生成的持久化列，把“空值”转换成一定不重复的值，让普通唯一索引去约束“非空值”。

思路是这样的：新增一个计算列，当原列值为 NULL 时，给一个由主键拼接的前缀，比如'NULL#' + CAST(UserId AS VARCHAR)，当原列非空时，直接返回原值。这个计算列的值永远不会重复（因为主键确定），再对它建普通唯一索引即可。

ALTER TABLE dbo.Users ADD InviteCode_UQ AS ( CASE WHEN InviteCode IS NULL THEN 'NULL#' + CAST(UserId AS VARCHAR(20)) ELSE 'NULL#' + InviteCode END ) PERSISTED; CREATE UNIQUE NONCLUSTERED INDEX UQ_Users_InviteCode_UQ ON dbo.Users(InviteCode_UQ);

看到没有，这里我故意把非空值也加了个'NULL#'前缀，是为了统一格式，避免原始值本身恰好和生成规则撞车，比如有人填了一个NULL#5的邀请码，就会和某个 NULL 用户的生成值冲突。加上前缀能降低这种冲突概率，但并不能 100% 消除。

所以这个方案有一个隐患：如果业务允许用户填写NULL#5这种字符串，恰好碰到 NULL 用户的生成值，就会产生假冲突。解决办法是使用不可能出现在业务里的前缀，或者延长拼接串。比如用'~|NULL|~#'这种几乎不可能作为业务值的字符串。

还有一个缺点是表结构多了一个列，占用空间，并且每次读写多一次计算。但它的优势也非常明显：不需要触发器，完全通过索引的强约束保证一致性，对老版本 SQL Server 也适用。

3.4 三方案横向对比

从表格可以看出来，能上过滤索引就千万别犹豫。触发器和生成列更像是“环境不允许时的幸存方案”。

4. 实操记录：从建表到验证

4.1 建表与插入测试数据

我们直接用前面建好的表来做一轮完整测试，看看这个约束到底是如何生效的。

-- 1. 插入 NULL 值，多个 NULL 应该成功 INSERT INTO dbo.Users (UserName, InviteCode) VALUES ('张三', NULL); INSERT INTO dbo.Users (UserName, InviteCode) VALUES ('李四', NULL); INSERT INTO dbo.Users (UserName, InviteCode) VALUES ('王五', NULL); -- 查看当前数据 SELECT * FROM dbo.Users;

执行完这三个插入，你会发现三条记录都正常入库，所有 InviteCode 都是 NULL，完全没问题。这验证了“空值不唯一”的预期。

4.2 验证约束生效

接着我们尝试插入非空重复值和唯一值：

-- 4. 插入唯一非空值，应该成功 INSERT INTO dbo.Users (UserName, InviteCode) VALUES ('赵六', 'INV-001'); -- 5. 再次插入相同非空值，应该失败 INSERT INTO dbo.Users (UserName, InviteCode) VALUES ('孙七', 'INV-001');

到第 5 条，SQL Server 会抛出类似这样的错误：

违反了 UNIQUE KEY 约束“UQ_Users_InviteCode”。不能在对象“dbo.Users”中插入重复键。

这就是过滤唯一索引在起作用。注意错误信息里写的是“UNIQUE KEY 约束”，但实际上它是索引，不是约束，只是错误提示用词不太准确，不用纠结。

再测试一个边界：把 NULL 和空字符串混着插入。

-- 6. 插入空字符串，成功，它和 NULL 不冲突 INSERT INTO dbo.Users (UserName, InviteCode) VALUES ('周八', ''); -- 7. 再插入一个空字符串，应该失败，因为空字符串不是 NULL INSERT INTO dbo.Users (UserName, InviteCode) VALUES ('吴九', '');

第 7 条会失败，因为空字符串是一个普通值，会被过滤索引拦下来。这个行为提醒了大家：如果你希望空字符串也代表“未填写”，那得提前在应用层统一转成 NULL，或者把过滤索引的 WHERE 条件改成InviteCode IS NOT NULL AND InviteCode <> ''。

4.3 已有脏数据时怎么建索引

很多人建索引的时候会发现，明明想加过滤唯一索引，结果执行却报错。最常见的原因就是：表里已经有重复的非 NULL 数据，或者重复的非空字符串。

解决办法分两步。

第一步，找出重复值。写一条 GROUP BY 查询：

SELECT InviteCode, COUNT(*) AS 重复次数 FROM dbo.Users WHERE InviteCode IS NOT NULL GROUP BY InviteCode HAVING COUNT(*) > 1;

第二步，处理重复数据。通常有三种做法：

• 人工去重：将重复数据的其中一个值改成随机不重复的编码，或改成 NULL；
• 应用层去重：针对重复项通过业务规则合并或归档；
• 直接清掉无关紧要的测试数据。

处理完之后，再执行创建索引的语句。

这里分享一个小技巧：如果表非常大，创建过滤索引可能会阻塞线上写入。可以先用ONLINE = ON的选项创建在线索引，这样建索引期间，表依然可以正常查询和写入。语法如下：

CREATE UNIQUE NONCLUSTERED INDEX UQ_Users_InviteCode ON dbo.Users(InviteCode) WHERE InviteCode IS NOT NULL WITH (ONLINE = ON);

不过要注意，ONLINE 模式只在 Enterprise 或 Developer 版里可用，Standard 版对某些索引操作仍会锁表，实际环境里得先确认版本。

4.4 索引维护与统计信息

过滤索引也是索引，日常维护不能少。定期更新统计信息会让优化器得到更准确的估算：

UPDATE STATISTICS dbo.Users UQ_Users_InviteCode;

碎片整理也一样，跟普通索引没有区别。实际使用中，过滤索引因为数据量通常比全表小，碎片的增长速度通常更慢，维护压力反而低一些。

另外，要特别留意索引统计信息对查询计划的影响。过滤索引的统计信息只统计满足条件的行，当条件列的选择性很高（几乎没有重复）时，优化器会得出“非常精准”的结论，这是好事。但如果这个字段长期处于高重复状态，统计信息会一直保持偏态，优化器可能基于错误的密度估算选择全表扫描，最终查询性能变差。遇到这种情况，手工更新统计信息就能缓解。

5. 常见问题与排查实录

5.1 创建索引报重复键 1505

如果你看到错误号 1505，说明表里已经有重复数据，过滤索引创建不进去。检查方法在前一节已经说了，不再追述一遍。

这里再补充一个排查技巧：用系统视图快速检查到底是哪个索引、哪列冲突。SQL Server 的错误信息里会写明索引名和重复键值，但如果你用了大小写敏感或排序规则比较特殊的字段，重复键值的显示可能不直观，建议直接用 GROUP BY 查询核对。

5.2 过滤索引没被查询使用

过滤索引要求查询语句里带上和 WHERE 条件匹配的谓词，优化器才会认真考虑它。

举个例子，索引过滤条件是WHERE InviteCode IS NOT NULL，你查询时写：

SELECT * FROM dbo.Users WHERE InviteCode = 'INV-001';

优化器能推断出InviteCode = 'INV-001'必然属于非空集合，所以理论上可以使用过滤索引。很多时候实际也会用。但如果你写：

SELECT * FROM dbo.Users WHERE ISNULL(InviteCode, '') = 'INV-001';

那优化器就不好推断筛选结果了，索引可用性变差。所以写查询时，尽量保证谓词形式简单，且和索引过滤条件有一目了然的关系。

要是你发现明明查询该走索引却没走，把执行计划打开一看，发现回表了，也很正常。过滤索引往往只覆盖了非空数据子集，如果查询还需要回表拿其他列，优化器会权衡才行。如果你只查 InviteCode 和 UserId，可以考虑把这两列做成包含列索引，进一步提升效率。

5.3 和 ORM 框架、EF 打交道

使用 Entity Framework 或别的 ORM 时，过滤索引不影响常规的增删改查，它只是数据库对象，不会暴露给客户端代码。你唯一要操心的是：如果应用层有唯一校验逻辑，它和过滤索引的规则可能不一致。

比如应用层可能用“先查再插”的方式判断编码是否重复，但因为多个 NULL 是合法的，应用层的查询条件也得注意IS NULL的判断要谨慎。否则应用层以为“有重复”，实际数据库根本没拦。

另外，EF Core 的迁移功能会自动生成索引创建语句，但它不感知过滤索引。如果你在迁移里手动写了过滤索引，后续的迁移脚手架可能会尝试将其删除或重建，要注意迁移文件的一致性。

5.4 过滤索引的版本与兼容性限制

过滤索引从 SQL Server 2008 开始支持，这是一个值得庆幸的版本。如果你的业务还在 2005 上，那就只能回到触发器或生成列方案。

需要注意的是，过滤索引对下面这些功能有限制：

• 不能用于索引视图；
• 不能在计算列上直接创建过滤索引（要先持久化计算列）；
• 不能和某些数据压缩选项一起配合使用，需要查版本文档确认；
• 在分区表上，过滤索引的规则比普通索引复杂，建议先在测试环境验证。

这些限制在日常开发里碰到的概率不大，但一旦碰上，基本就是要重新设计索引的程度，最好提前意识到。

个人在实际操作中的体会是，这类“空值可重复、非空值唯一”的需求，用过滤唯一索引是最省心的。它能彻底取代过去那些别扭的触发器、累赘的生成列，而且语义清晰，以后维护的人一眼就能看懂你要干什么。

最后再分享一个变体需求：当时某项目还有个类似场景，“未发布”的文章允许标题重复，“已发布”的文章标题必须唯一。这就是把过滤条件换成WHERE IsPublished = 1，其他逻辑一模一样。这种灵活扩展是普通唯一约束完全做不到的，也是我推荐大家尽量掌握过滤索引的根本原因。