unique constraint on a large column

• UncleBoris

  SSCrazy

  Points: 2018

  More actions

  April 10, 2013 at 7:45 pm

  #292087

  I have a table with a column of NVARCHAR(2000) in size.

  There is a requirement to ensure the data in the column is unique.

  The table contains 170,550 rows.

  A unique constraint fails because it violates the 900 bytes max key size field.

  So what database options do I have?

  I tried using the CHECKSUM function on the offending field but I can only guess this is only a few bytes in size because over the 170,550 rows there is 128 collisions.

  So I tried a SHA1 checksum and this seemed to work Ok

  ALTER TABLE [testtable] ADD SHA1 AS HashBytes('SHA1', CONVERT(VARCHAR(1000),big_field))

  Would this be the best way to enforce uniqueness of have I missed something simple..thanks

• sharky

  Default port

  Points: 1494

  More actions

  April 11, 2013 at 12:06 am

  #1605475

  I would also use a hashbyte function. In theory there still might be some duplicates, although unlikely.