I have multiple nvarchar(max) columns in my DB named ShipperName,ConsigneeName, ProdDesc,BillOFLading.

What I need to do is to have UNIQUE INDEX for the combination of these columns. As I am failed to do so because if 900 B limit.

Then I tried(after reading following link Indexing Wide Keys )

ALTER TABLE dbo.ProductDetail 
   ADD ShipperNameHash AS HASHBYTES('SHA2_512', ShipperName) PERSISTED;

ALTER TABLE dbo.ProductDetail 
   ADD ConsigneeNameHash AS HASHBYTES('SHA2_512', ConsigneeName) PERSISTED;

Then I got following error on following statement:

Msg 8152, Level 16, State 10, Line 1
String or binary data would be truncated.
The statement has been terminated.

ALTER TABLE dbo.ProductDetail 
   ADD ProductDescHash AS HASHBYTES('SHA2_512', Product_Description) PERSISTED;

But also above statements created column varbinary(8000). Which of course hasn't solved the problem for me.

Is there any way that I can built UNIQUE INDEX on the combination these columns ?


One thing you might try is breaking those fields out into their own table(s). Then reference them by ID. You could then set up a UNIQUE index on those ID columns, and still use your hashing trick to ensure you have unique values in the new tables (if the values need to be unique there).

Example table(s):

 ShipperID INT,
 ShipperName NVARCHAR(MAX)

 ConsigneeID INT,
 ConsigneeName NVARCHAR(MAX)


Unfortunately, with this type of design problem you have to get a little creative. Having that many NVARCHAR(MAX) columns is problematic on many levels.

  • Yeah this is designed very poorly which is giving problem in maintainance. Sir what I did for right now is that I have created CHECKSUM() for these fields and used them for creating INDEX so I have increased the no of columns in index to increase their permutations. – analyticalpicasso Feb 27 '14 at 13:24
  • 1
    @agnes I agree moving these repeated long strings out of ProductDetail is a good move. I would also argue that chances are there is no need to hash these at all. Is a shipper name every really going to be longer than 450 characters? If not you can just use nvarchar(450) instead of max and stick a unique constraint on that. Even 450 is extremely generous. I can't imagine any sensible business would have a name even 100 chars long. – Martin Smith Feb 27 '14 at 13:50
  • @MartinSmith Yeah I have already decreased Shipper_Name & Consignee_Name to standard nvarchar(510) but still its not gonna work because before UNIQUE INSERTion I need to compare all above mentioned columns. I needed to HASH them because of 900 B limit. – analyticalpicasso Feb 27 '14 at 15:06

I know this is already answered, but I'm going to put in my vote for using HASHBYTES. I talk a little about it in my post http://michaeljswart.com/2013/11/hashing-for-indexes/ and I describe what that looks like in this case below:

If you can get away with nvarchar(4000) instead of nvarchar(max), then, this schema becomes feasible:

CREATE TABLE ProductDetail
    ShipperName NVARCHAR(4000),
    ShipperNameHash as HASHBYTES('SHA1', ShipperName) PERSISTED,
    ConsigneeName  NVARCHAR(4000),
    ConsigneeNameHash  as HASHBYTES('SHA1', ConsigneeName) PERSISTED,
    UNIQUE(ShipperNameHash, ConsigneeNameHash )

When creating it, SQL Server will warn you about the maximum key length for that unique index being over 900 bytes. But in practice, SHA1 never returns more than 20 bytes, so in this particular case, the warning is ignorable.

See that it works with this insert statement

insert ProductDetail (ShipperName, ConsigneeName)
  REPLICATE(N'A', 3999) + N'B', 
  REPLICATE(N'A', 3999) + N'B'
  REPLICATE(N'A', 3999) + N'C', 
  REPLICATE(N'A', 3999) + N'C'

See that it fails with this insert statement:

insert ProductDetail (ShipperName, ConsigneeName)
  REPLICATE(N'A', 4000),
  REPLICATE(N'A', 4000)
  REPLICATE(N'A', 4000),
  REPLICATE(N'A', 4000)

I've noticed you've gone for the CHECKSUM() solution, however you need to be aware that CHECKSUM() does not produce a unique value (see Hash functions in T-SQL) and that it is possible to generate collisions that will violate your unique index.

If you're going down the hashing route, use HASHBYTES() as there is less chance of you generating colliding values

  • She did attempt this but it did not work as she was attempting to create a HASH per column, though she should be able to create a hash for the concatenation of the 4, correct? – Mark Wilkinson Feb 27 '14 at 13:56
  • 2
    If you're concatenating, you'll have to check for NULLs as HASHBYTES will return NULL for the whole value if any one column is NULL – Mark Sinkinson Feb 27 '14 at 14:31
  • @MarkSinkinson I needed to choose CHECKSUM() just because the no of bytes it was taking although my first and foremost choice was HASHBYTES(), which was not solving my ultimate problem. As I know that we can get collisions in CHECKSUM() so what I did is I have added some columns in INDEX for it to be UNIQUE + with addition of column I might decrease some chance of collision as its permutation has increased. Please correct me if I am wrong in any way. – analyticalpicasso Feb 27 '14 at 15:18

Short answer: no.

If the values are sufficiently different, you might be able to get away with creating partial value columns (for example using left() or right()) and indexing over those.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.