如何在 SQL Server 中对 NVARCHAR(MAX) 列强制执行唯一性？

Question

我在 SQL Server 2008 R2 中有一个 NVARCHAR(MAX) 类型的列。我想在该列上设置唯一索引，但是作为唯一索引的列的最大大小为 NVARCHAR(450) 或 900 字节。

如何对 NVARCHAR(MAX) 列强制执行唯一性？

谢谢！

- Kuyenda

---

可能是为什么无法对nvarchar(max)设置约束？的重复问题。 - Yuck

你实际上不能这样做 - 请参考以上内容并投票关闭。 - Yuck

请参考以下内容：https://dev59.com/jHM_5IYBdhLWcg3w4nfb - prusswan

可能有一种方法可以绕过内置的限制。这个问题是关于强制唯一性，而不一定是在字段上添加约束。 - JNK

@JNK：微妙……我想你可能是对的。 - Yuck

这个问题可能已经在其他地方被问到了，但它与“为什么不能对nvarchar(max)设置约束”是不同的问题。我认为Lucero很好地回答了所提出的问题。 - Michael J Swart

Answer 1

1. Create a persisted column on some kind of hash, such as CHECKSUM. For example: Hash = MyStringHash as CHECKSUM(MyString)
2. Create a non-unique index on that column

3. Create a trigger that enforces uniqueness. The index is needed to speed up the search in EXISTS clause:

           WHERE   NOT EXISTS ( SELECT 1
                                FROM   YourTable AS y
                                WHERE  y.MyStringHash = CHECKSUM(Inserted.MyString)
                                       AND y.MyString = Inserted.MyString) ;
   

重要提示：您需要根据您的排序规则进行测试。如果您使用的是不区分大小写的排序规则，请确保触发器不允许同时出现'MyTest'和'MYTEST'。

如果您只创建唯一索引并停止，那么您只是在等待出现错误的漏洞。

编辑：在不区分大小写的环境中，我使用了CHECKSUM用于持久计算列，这是快速、不区分大小写且足够有选择性的方法。

- A-K

---

Alex，我假设你想在一个计算列中使用CHECKSUM。感谢你提供的解决方案！ :) - Kuyenda

CHECKSUM是一个非常糟糕的选择：碰撞的几率很高。 - spaghettidba

Answer 2

我和Cory有同样的想法。假设与SHA1碰撞的可能性足够小，你可以按照以下步骤操作:

CREATE TABLE [dbo].[tblHash](
    [s] [nvarchar](MAX) NOT NULL,
    [h]  AS (hashbytes('SHA1',[s])) PERSISTED,
    CONSTRAINT [IX_tblHash] UNIQUE NONCLUSTERED (
        [h]
    )
)

- Lucero

---

无法工作。Hashbytes 仅限于 8k 字节。您需要编写自己的 hashbytes，以处理超过 8k 字节的数据，并且需要考虑它的速度较慢。http://msdn.microsoft.com/en-us/library/ms174415.aspx - "允许输入值的大小限制为 8000 字节"。 - David Eison

Answer 3

您如何拥有一个如此大的字段，以至于您担心会出现重复？

一种方法是创建一个计算字段，例如LEFT(NvarCharMaxField, 200)，并对该字段放置唯一索引。除此之外，我认为没有其他方法，除非您添加触发器，但那将非常昂贵。

- JNK

---

同意，nvarchar(max)字段起初不应该需要唯一索引。这个字段中存储了什么内容？能否将其重构为可以建立索引的内容？ - HLGEM

Answer 4

您可以对NVARCHAR(MAX)字段使用某种哈希函数，并在哈希字段上创建唯一索引。

编辑：

正如Yuck所指出的那样，由于哈希函数的工作方式，您可能会遇到冲突。 如果您使用SHA1等哈希，则可能性非常小，但仍有可能发生冲突。

- Cory

---

与任何哈希函数一样，可能会发生冲突，因此这可能会误导。 - Yuck

@Yuck，像SHA1这样的加密哈希算法应该足够不可能发生碰撞。 - Lucero

根据@Lucero所说的，编辑你的答案，这样我就可以取消踩了。 - Yuck