我正在使用SQL Server 2008。
我有一个 名为Title 的 NVARCHAR(MAX)列,我想为其添加一个唯一索引。因为该列大于900bytes,所以我决定创建一个HashBytes计算列。
如何创建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
VARBINARY(MAX)除非您明确告诉它20字节已足够,否则将创建hashbytes列作为a :
VARBINARY(MAX)
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)
现在,这应该可以正常工作。