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
So hashbyte should always take all columns, exclude identity. This is manual way typing all the columns: CREATE TABLE [dbo]. [CustomerTransactiont] ( CustomerTransactionId int primary key identity (1,1), CustomerName varchar (255), Price decimal (10,2), Quantity int,... .....
The BigHashBytes is only necessary if the length would be over 8k. 1) String data in SQL Server is stored as UTF-16 Little Endian, which equates to "Unicode" in .NET. 2) You don't need to mess with Encoding.<encoding_name> since SqlString can just give you the Unicode byte [] via SqlString.GetUnicodeBytes.
A computed column is a virtual column that is not physically stored in the table, unless the column is marked PERSISTED. A computed column expression can use data from other columns to calculate a value for the column to which it belongs.
You will need to set up the HashValue column to use two parameters. The first is the algorithm (e.g. SHA1); the second is the unique value to be hashed. I suggest concatenating all your values with a suitable separator to ensure the same value in an adjacent column is not hashed the same.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With