A creative use of IGNORE_DUP_KEY

re: A creative use of IGNORE_DUP_KEY

tobi — Fri, 01 Feb 2013 21:55:17 GMT

Amazing post, also looking forward to the post about Merge and Halloween Protection.

Another alternative to the "duplicate" UNIQUE index might be a trigger. It might be slower for DML but does not require storage space.

re: A creative use of IGNORE_DUP_KEY

wqw — Tue, 05 Feb 2013 19:16:05 GMT

Warning on IGNORE_DUP_KEY was raised as an *error* by current SQLOLEDB/ODBC drivers so it is impossible to use it in client applications. Never tested recent NCLI/.Net Providers but possibly the issue is fixed.

re: A creative use of IGNORE_DUP_KEY

Paul White — Tue, 05 Feb 2013 22:45:25 GMT

@tobi An INSTEAD OF trigger? I guess so, though the overheads are even worse than the extra operators in the plans I show here. Both INSTEAD OF and AFTER triggers require storage for the inserted and deleted tables.

@wqw Thanks, good to know.

re: A creative use of IGNORE_DUP_KEY

Paul White — Thu, 14 Feb 2013 02:06:10 GMT

The first part of the series on the Halloween Problem is now out:

http://www.sqlperformance.com/2013/02/t-sql-queries/halloween-problem-part-1

re: A creative use of IGNORE_DUP_KEY

wqw — Fri, 15 Feb 2013 16:22:18 GMT

FYI, on SQL Azure database `WITH IGNORE_DUP_KEY` option raises "Deprecated feature 'INDEX_OPTION' is not supported in this version of SQL Server."

re: A creative use of IGNORE_DUP_KEY

Paul White — Sat, 16 Feb 2013 10:23:03 GMT

wqw,

Interesting. Another thing: MERGE ignores IGNORE_DUP_KEY for insert operations too. Thanks for the Azure information.

re: A creative use of IGNORE_DUP_KEY

sqlscripter - Ed P — Tue, 12 Mar 2013 16:50:09 GMT

Neat article, I had to stuff 5 databases into 1 ofcourse for a "special" reporting db and got that same error years ago. Funny no one could get it to work for a long time I was told, I found the INGORE_DUP_KEY option and blingo....everyone in the pool...problem solved