Microsoft KB Archive/301113

= BUG: BOL Incorrectly States That IGNORE_DUP_KEY Affects UPDATE Statements =

Article ID: 301113

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q301113



BUG #: 354426 (SHILOH_BUGS)



SYMPTOMS
The &quot;Creating a Unique Index&quot; section of SQL Server Books Online (BOL) incorrectly states that the IGNORE_DUP_KEY option can affect UPDATE statements:

&quot;When you create or modify a unique index, you can set an option to ignore duplicate keys. If this option is set and you attempt to create duplicate keys by adding or updating data that affects multiple rows (with the INSERT or UPDATE statement), the row that causes the duplicates is not added or, in the case of an update, discarded.&quot;

This is incorrect. If you attempt an UPDATE statement that would result in duplicate rows, the entire UPDATE statement will be rolled back.



STATUS
Microsoft has confirmed this to be a problem in the SQL Server 2000 documentation.



MORE INFORMATION
The following script shows that when IGNORE_DUP_KEY is used, an INSERT statement discards only the rows that would be duplicates, whereas the entire UPDATE statement is rolled back. USE tempdb GO DROP TABLE test1 DROP TABLE test2 go CREATE TABLE test1 (col1 CHAR(5), col2 INT) CREATE TABLE test2 (col1 CHAR(5), col2 INT) go INSERT test1 VALUES('one', 1) INSERT test1 VALUES('two', 2) INSERT test1 VALUES('three', 3) INSERT test2 VALUES('four', 2) INSERT test2 VALUES('three', 1) go CREATE UNIQUE INDEX index1 ON test1(col1) WITH IGNORE_DUP_KEY CREATE UNIQUE INDEX index2 ON test2(col1) go SELECT * FROM test1 SELECT * FROM test2 go UPDATE test1 SET test1.col1=test2.col1 FROM test1 INNER JOIN test2 ON test1.col2=test2.col2 SELECT * FROM test1 GO INSERT test1 SELECT * FROM test2 GO SELECT * FROM test1 GO

Keywords: kbbug kbpending KB301113

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.