John Mitchell-245523 (6/8/2012)
I got this right, but I'm still a little confused. Am I correct in thinking that what is being tested here is knowledge of the IGNORE_DUP_KEY setting and not anything to do with transactions? Since IGNORE_DUP_KEY means that there is no error, it doesn't matter whether the statements are wrapped in a transaction or not. Furthermore, as we saw in previous questions in this series, the transaction would commit regardless of a unique key violation, unless XACT_ABORT is set to OFF, which is not the default.John
Edit: didn't mean to parrot what was written in the previous few posts - they weren't there when I started writing this one!
John, the difference is visible when you try to insert all those rows in one transaction like
INSERT INTO [QOTD4] ([col1], [col2], [col3])
SELECT 1, 'w', 'something'
UNION ALL
SELECT 2, 'x', 'or other'
UNION ALL
SELECT 1, 'y', 'thing'
UNION ALL
SELECT 3, 'z', 'or what'
With IGNORE_DUP_KEY = OFF the result would be that there are 0 records inserted. When IGNORE_DUP_KEY = ON it results in 3 records being inserted.