David Burrows (10/15/2013)
Hugo Kornelis (10/14/2013)
Yes, technically the rows were inserted and then rolled back. But from a logical point of view, the inserts never happened. Which makes the answer correct.
The answer was 'no records inserted into table' which is factually incorrect, irrespective of any 'logical point of view' or final result of the batches.
As I has already stated I was not criticising the question, I took the answer in the spirit the way the question was phrased.
I suppose we will have to agree to disagree 😀
This is the first occasion I've been confident enough to disagree with Hugo. The fact that a read WITH NOLOCK could have picked up the rows means that they did exist and therefore were inserted. I can make no technical or logic distinction here. It's like if my building society makes a mistake and credits me with £1,000,000 and then realises its mistake and debits the money from my account. At the end of the day, I'm not £1m better off but the credit and debit did happen.
However, as David has intimated, the answer is correct in the spirit of the question.
re: The £1,000,000 pounds - true. In that case, the transaction has been committed to the database; the money was in your account. The funds were wired back later, when the mistake was discovered.
However, if there is a check (or other constraint) that limits transfers to your account to a maximum of £250,000, the faulty transfer would have been rejected because the constraint is violated. This data violates a business rule, so it can never exist in the database. The details of the constraints checking are not defined in the relational model; this is an implementation choice. SQL Server happens to make the choice to first insert the rows (with locks to prevent others from seeing data that might not be able to actually exist) and then undo the change if a constraint is violated. It's just as possible to do all the checks first, and only actually insert the rows after the checks are passed. In one case, the row is technically inserted in the database, then removed again; in the other case, the row is technically never inserted at all. But logically, there is no difference - the row never existed.
On the possibility of a NOLOCK read picking it up - I believe that was exactly my point. People warn against using NOLOCK because it can pick up changes that never really existed in the database. (In fact, a NOLOCK read can even pick up inserts and deletes the not only didn't happen logically, but also inserts and deletes that never happened technically either - I have seen code where a SELECT COUNT(*) FROM SomeTable WITH NOLOCK produced incorrect results as a result of another connection that was not doing inserts or deletes, but only updates...)
Just for the record:
1. I am not disagreeing with you. Just stating that you can look at this from two angles. From the technical, implementation-dependent angle, as you do, but also from the high-level, relational angle - Codd's eighth rule.
2. Please don't hesitate to disagree with me more often. I'm wrong quite often, as my children will be happy to tell you. If you have a different POV from me, don't be afraid to point it out. I might or might not respond - but in any case, at least one of us will learn, and probably we both. (And all others who read the discussion).