| Downloads |
|---|
| 96785.zip |
|
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
