Seth Kramer (12/18/2010)I accept I got it wrong, but what do you mean the second transaction fails? Only the insert null into the primary key field statement fails. There are no failed inserts on Table2.

It looks to me as if the explanation paragraph following the reference to BoL has got sucked in from some other explanation, as it certainly has no connection with this question. Also, the wording of the possible answers is horrible and could easily have been fixed by changing "where" to "last" and sticking a comma in front of it.At first I thought I would have to rack my brains and remember how nulls are treated by ORDER BY, but then I noticed that there were only two options with 7 rows and neither of them had NULL as the last d value, so the ordering of nulls was irrelevant after all and it was actually a no-brainer question.

Bradley Deem (12/20/2010)Ian, I know you've received an explanation already, but I wanted to add to that. You can control the behavior on error as follows in the comments below.

BEGIN TRAN INSERT INTO [Table1]( a,b) VALUES(1 ,'one')

INSERT INTO [Table1]( a,b) -- Insert fails because the NOT NULL constraint on column [a] VALUES(NULL ,'five') -- Even though the above insert STATEMENT fails, execution of the BATCH continues. -- To halt execution after error use SET XACT_ABORT = ON (stops the BATCH from executing) -- To reroute execution after error use TRY CATCH (recommended) or check @@Error and use GOTO

INSERT INTO [Table1]( a,b) VALUES(4 ,'join1')

INSERT INTO [Table1]( a,b]) VALUES(2 ,'join3')COMMIT TRANGO

That's a good explanation, thank you. I'm usually a bit lazy with my error handling and use of transactions, hence my confusion.

I don't call it a good question, because of the complexity - not the complexity of the subject matter, but that of the number of statements, and the number of rows. We had to deal with behaviour of a failed insert in a transaction, then the behaviour of two kinds of joins, union, ordering, and the position of NULL values in an order by (which, for the record, is not the same in all RDBMS's; whether NULLs go first or last is not defined in the ANSI standard, but left as an implementation-dependant choice). And all that on tables with three or four rows.

I think a good QotD should test one, maybe two subjects only. And preferably with a combination of statement complexity and number of rows that enables those with an understanding of the subject to work out the results in their head. This one severely pushed my ability to work it out in my head.

SanDroid, I'd love to see more questions from you, as the idea for this question is great - but as a suggestion for the next time, consider using two or three rows in the tables, not more. And don't add extra complexity by adding weird primary keys, failed inserts, etc. Just giving the table population, a query with a join on nullable columns, and some believable but incorrect answer options would have been enough.

As to the erroneous explanation - well, you already explained that this is the result of changes to question and explanation getting out of sync. Stuff like that happens, 'nuff said.

:) don't worry, it happened to me too, I did not read carefully the question. Morale of the story: it is better to pay attention at the requirements and just not knowing the correct answer, than missing the question and answer the wrong thing. However, the explanation they provided, that 'no rows are inserted in the Table2' is completely bizarre. (repairing frozen pipes qualify for not reading carefully)