Jeff Moden wrote: Jeffrey Williams wrote: Jeff Moden wrote:
Gosh... I agree that it's been quite a while but it seems to me that SQL Server 6.5 and 7 worked just fine in this area and I've not had cause to need such functionality since then, but I wonder why they felt compelled to change it.
If SQL Server 6.5 and 7 worked that way - then it was incorrect. An insert or update statement is implemented as a single transaction that occurs at a specific time and all rows affected are affected as if the transaction occurred all at once, even if the actual insert or update were to take several hours to complete.
Opinion noted. I strongly disagree but noted. 😀 Like I said, it's been a long time since I've needed such a thing but it would be nice to have the choice. And, don't say it doesn't need to be done. You may not know of a reason to do it but someone else (like the OP) may have a perfectly valid reason to do it just as surely as you'd need RAND() to produce a different value for each row upon insert or, even better, you have a UDF (inline or otherwise) use the return of NEWID() or the return of the output of a stored procedure. While it may not usually be something that people would need to do and would sometimes have to be very careful about how they used such a thing, it is nice to have such options (all of the above, which I've used a lot for more than one reason).
It's like saying that overloading a variable in a SELECT statement is a bad thing... when I briefly used Oracle (only 3 years), that pithy little restriction caused me a huge amount of development pain. I did it all the time in SQL Server and still do because SQL Server didn't have the ability to do string aggregation. Even the FOR XML PATH hack had some major pitfalls that were easily avoided by variable overloading.
When you said "If SQL Server 6.5 and 7 worked that way - then it was incorrect.", the only thing that was incorrect there was you thinking so. You have to understand that, as always and mostly forever, "It Depends". 😉
Insrting a row is more than inserting a row.
there is an indexing update involved also. Bulk insert gives you a luxury of redoing index once per the whole set, but it means all records are in the same boat, waiting to be included into updated index at once.
There is also such thing as parallel execution. Which ruins the beautiful world of those developers who believe a clustered index guarantees the order of rows in a table. With 2 or more parallel insert processes none of them is finalised until all of them are stitched together.
Therefore, to me, it makes perfect sense to not distinguish insert timing for different rows in the same INSERT set. They all start together, and none of them is done until all of them are done.
In the end of the day, it's under one of those letters in the acronym ACID. Kinda required.