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