SQL Server 2008 - HashBytes computed column

Question

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

Solution

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.

Comments

[Denis Valeev]

Post the index creation statement.

[Denis Valeev]

Good question and good answer. And I learned not to cast to bigint today myself.

[Michael J Swart]

Great question. But we got burned in the past because we'd never paid too much attention to SET Option Requirements before. msdn.microsoft.com/en-us/library/ms189292.aspx is the official docs and pay careful careful attention to the section called "SET Option Requirements"