SQL Server 2008 - HashBytes computed column

问题:

I'm using SQL Server 2008.

I have a NVARCHAR(MAX) column called Title and i want to add an unique index for it. Because the column is bigger than 900bytes , I decided to create a HashBytes computed column (based on recommendation on StackOverflow).

How do i create the HashBytes column?

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

this worked and the computed column was created.

BUT when trying to add a index i get the following error:

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?

This is the query used to create the index:

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

回答1:

The hashbytes column gets created as a VARBINARY(MAX) unless you do specifically tell it that 20 bytes are plenty:

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

Once you've done that, then you can create your index (unique or not) on that column:

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

Now this should work just fine.