Enforce Uniqueness Without the Unique Constraint


Executive Summary:

There are many ways to concatenate row values in Microsoft SQL Server databases. With the hope of finding the most efficient row-concatenation solution, a SQL Server Magazine reader tested two common approaches: using a self-reference variable within a SELECT clause and using FOR XML. He found that although these solutions produce the same result, the time it takes to get those results varies significantly.

If you’ve worked as a DBA for a while, you might have come across the problem of hitting the 900-bytes limit for unique constraints. I’ve developed a way to work around this limitation.

To work around the 900-byte limit, you can create an additional computed column that gives the checksum value of the string column. Checksum is an int data type, which means you can create a unique index for the computed column. Listing 1 shows sample code for this solution, which works on SQL Server 2005 and SQL Server 2000. In this case, a computed checksum column is created for the msg_hdr string column.

If you run the code in Listing 2, which inserts three rows without any duplicates and a fourth row with a duplicate msg_hdr, you get the error message: Msg 2601, Level 14, State 1, Line 5. Cannot insert duplicate key row in object ‘dbo.messages’ with unique index ‘msg_hdr_uk’. The statement has been terminated. As this message shows, the unique index enforces the uniqueness and fails the offending INSERT statement.

—Aviv Zucker, Intel

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.