Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
    
I suspect you would be happier with Full-Text Search instead. –  RBarryYoung Feb 27 at 21:35
    
@RBarryYoung Wont it add overhead if I just need UNIQUE INDEX ? –  null Feb 28 at 5:54
    
Yes, that's true. I didn't quite understand that implementing a uniqueness constraint was your only need here. It's also a little odd to be using such a huge amount of text for uniqueness, since even a single extra space anywhere in up to 8GB would cause it to be different. Though i suppose that could make sense if you're using it to detect un-logged editing differences for versioning/revision identification. –  RBarryYoung Feb 28 at 15:27
    
@RBarryYoung Yes Indeed this table is populating through excel records. creating UNIQUE INDEX was actually the initial step, as I have to maintain n restructure this whole unstructured, unkempt db. –  null Mar 3 at 18:32
add comment

4 Answers

up vote 4 down vote accepted

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):

[Shipper]
 ShipperID INT,
 ShipperName NVARCHAR(MAX)

[Consignee]
 ConsigneeID INT,
 ConsigneeName NVARCHAR(MAX)

ETC...

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.

share|improve this answer
    
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. –  null Feb 27 at 13:24
    
As I said: creative solutions. :) I hope it works out for you. –  Mark Wilkinson Feb 27 at 13:26
    
:) Sometimes we learn a lot in maintaining too :) Thanks! –  null Feb 27 at 13:28
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 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. –  null Feb 27 at 15:06
add comment

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
(
    ProductDetailId INT IDENTITY PRIMARY KEY,
    ShipperName NVARCHAR(4000),
    ShipperNameHash as HASHBYTES('SHA1', ShipperName) PERSISTED,
    ConsigneeName  NVARCHAR(4000),
    ConsigneeNameHash  as HASHBYTES('SHA1', ConsigneeName) PERSISTED,
    UNIQUE(ShipperNameHash, ConsigneeNameHash )
)
go

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)
values 
(
  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)
values 
(
  REPLICATE(N'A', 4000),
  REPLICATE(N'A', 4000)
),
(
  REPLICATE(N'A', 4000),
  REPLICATE(N'A', 4000)
)
share|improve this answer
    
Great explanation sir. Cleared so many doubts with the blog link! –  null Feb 27 at 15:40
add comment

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

share|improve this answer
    
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 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 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. –  null Feb 27 at 15:18
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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