我正在使用SQL Server 2008。
我有一个称为Title的NVARCHAR(MAX)列,我想为其添加一个唯一索引。 因为该列大于900字节,我决定创建一个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
alter table Softs alter column
... - Denis Valeev