.net - SQL Server 2008 - HashBytes 计算列

Question

我使用的是 SQL Server 2008。

我有一个名为 Title 的 NVARCHAR(MAX) 列，我想为其添加唯一索引。由于该列大于 900bytes ，我决定创建一个 HashBytes 计算列(基于 StackOverflow 上的推荐)。

如何创建 HashBytes 列？

alter table Softs add TitleHash AS (hashbytes('SHA1',[Title])) PERSISTED;

这有效并创建了计算列。

但是当尝试添加索引时，我收到以下错误:

Adding the selected columns will result in an index key with a maximum length of 8000 bytes.  
The maximum permissible index length is 900 bytes. 
INSERT and UPDATE operations fail if the combined value of the key columns exceeds 900 bytes.  
Do you want to continue?

这是用于创建索引的查询:

CREATE NONCLUSTERED INDEX [UIX_TitleHash] ON [dbo].[Softs] 
(
    [TitleHash] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Answer 1

hashbytes 列被创建为 VARBINARY(MAX) 除非您明确告诉它 20 个字节就足够了:

alter table dbo.Softs 
  add TitleHash AS CAST(hashbytes('SHA1', [Title]) AS VARBINARY(20)) PERSISTED

完成此操作后，您可以在该列上创建索引(是否唯一):

CREATE UNIQUE NONCLUSTERED INDEX [UIX_TitleHash] 
  ON [dbo].[Softs]([TitleHash] ASC)

现在应该可以正常工作了。