siggemannen (1/28/2013)
Can SQL Server guarantee that if I issue a command like:
INSERT INTO T(...)
SELECT t.a, t.b, 1
FROM sometable t
UNION
SELECT -t.a, t.b, 1
FROM sometable t
, both "sides" of union will retrieve same table rows?
This looks like an isolation question to me.
If I understand correctly, you are asking can the first select be done, then in between the selects another process alters the table and then the second select is done.
On the isolation level serializable and snapshot isolation both tables will be read exactly the same.
On a dirty read level isolation (and a Nolock Hint), I think it is very possible that the two tables can be different.
For the isolation levels in between I am not sure. And I think it is difficult to prove that the tables CAN be different.
With Isolation level read commited, the table can be altered.
With the Isolation level repeatable read I think rows can be inserted, but not altered.
See:
Note that the capability to insert new "phantom" rows between locked rows that have already been scanned is the principle difference between the repeatable read and serializable isolation levels. A serializable scan acquires a key range lock which prevents the insertion of any new rows anywhere within the range (as well as the update or deletion of any existing rows within the range).
With serializable, the table can not be altered at all (no deletes, no updates and no inserts). Inserts are not possible until your transaction (or query) finishes.
With snapshot isolation, you get your own 'private' copy of the table at the start of the query. Somebody can insert, but this is not visible to the query.
You could test the different isolation levels, but that will take a lot of effort (I think). (Keep us informed).
This is my fast anwser, not totaly sure.
Ben Brugman