DOWNLOAD THE CODE:
Download the Code 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

End of Article


You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

It fails if you try to insert a duplicate value with case change.For example

insert into msgs values (1,'just to test messages') insert into msgs values (2,'Just to Test Messages') insert into msgs values (3,'Just to test messages')

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

nsr_chand- November 02, 2007

Article Rating 3 out of 5

great tip! thanks

jc30309atl- November 03, 2007

Article Rating 5 out of 5

Hi Aviv

I am interested with the article .

It is good for the beginning until I found that binary_checksum interpret same number between replicate (‘a’,2000) and replicate (‘b’, 2000) so it violates uniqueness constraint. Below is my script to produce for this bug.

CREATE TABLE dbo.c ( Colc INT NOT NULL PRIMARY KEY, Cola VARCHAR(2000), Colb AS BINARY_CHECKSUM(Cola) ); GO

CREATE UNIQUE INDEX IX_dbo_c on dbo.c(colb);

-- First insert INSERT DBO.C (colc, cola) values (1, replicate (‘a’,2000));

--Second insert will fail INSERT DBO.C (colc, cola) values (2, replicate(‘b’,2000));

Error message for the second insert : Cannot insert duplicate key row in object ‘dbo.c’ with unique index ‘IX_dbo_c’

The statement has been terminated.

So I think that solution using BINARY_CHECKSUM is not so reliable. Hopefully you have other better workaround for this.

Regards,

Kasim Wirama, MCDBA

wirama@cbn.net.id- November 22, 2007

Article Rating 3 out of 5

Hi Kasim, When using REPLICATE in your inserts, you have to convert to the right data type. In you example, change the INSERT statements to:

INSERT INTO messages(msg_id, msg_hdr) VALUES (1, CONVERT(VARCHAR, REPLICATE('A', 2000))) INSERT INTO messages(msg_id, msg_hdr) VALUES (2, CONVERT(VARCHAR, REPLICATE('B', 2000)))

and it will work.

Aviv.

azucker- November 27, 2007

Article Rating 5 out of 5

Hi Aviv,

Thanks for your response. I tried your workaround, and it works.

Here is a bit awkward thing I found here. Though replicate function returns same datatype as input datatype is (varchar-type input returns varchar-type output), still it has been had to be wrapped with convert/cast function to varchar to make it work properly.

Anyway, it is the best workaround to address my case.

Thanks for your solution.

Kasim Wirama, MCDBA

wirama@cbn.net.id- November 27, 2007

Article Rating 4 out of 5

Hi Aviv,

One more thing, I am wondering whether how to ignore case sensitivity as first feedback in Reader Comments column. So, if user enter "Just test" and "just test" (different case on first letter ; one is uppercase letter , the other is lowercase letter), and I would like to consider that they are same inputs so they violate unique constraint. Do you think checksum function is appropriately applied instead of binary_checksum?

Thank you.

Kasim Wirama, MCDBA

wirama@cbn.net.id- November 27, 2007

Article Rating 4 out of 5

I want to thank Kasim for writing about his experience with this script. It's great when others can benefit from your experience! Diana

DianaMay- November 27, 2007

Article Rating 5 out of 5

Hi Wirama, Regarding the case-sensitivity - you can use BINARY_CHECKSUM not on msg_hdr but rather on UPPER(msg_hdr), getting the behavior you are looking for:

CREATE TABLE messages ( msg_id INT NOT NULL PRIMARY KEY, msg_hdr VARCHAR(1024) NOT NULL, msg_hdr_checksum AS BINARY_CHECKSUM(UPPER(msg_hdr)) -- Note the change here ) GO

CREATE UNIQUE INDEX msg_hdr_uk ON messages(msg_hdr_checksum) GO

INSERT INTO messages(msg_id, msg_hdr) VALUES (1, 'Just test') (1 row(s) affected)

INSERT INTO messages(msg_id, msg_hdr) VALUES (2, 'just test') Msg 2601, Level 14, State 1, Line 3 Cannot insert duplicate key row in object 'dbo.messages' with unique index 'msg_hdr_uk'. The statement has been terminated.

(1 row(s) affected)

Aviv.

azucker- December 22, 2007

Article Rating 5 out of 5