January 28, 2013 at 12:13 am
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?
January 28, 2013 at 12:28 am
first the records from both tables will be clubbed then UNION will distinct the records.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 28, 2013 at 2:31 am
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?
SQLserver will never guarantee the order if no specific order by statement is given.
January 29, 2013 at 8:34 am
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?
Not sure what you mean by "same table rows", but as one other poster pointed out, UNION will return only one instance of a row that is returned by more than one of the UNIONed queries with identical values in each column. That won't ever happen in your example, assuming that "-t.a" actually returns the negative of the value in t.a. If you want all rows returned by the UNIONed queries, even if they return "duplicate" rows, use UNION ALL.
Also, I don't know if this pertains to your question, but each query in a UNION/UNION ALL can have its own WHERE clause to set the conditions for the rows to be returned by that query.
Jason Wolfkill
January 29, 2013 at 10:02 am
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
January 29, 2013 at 3:44 pm
I think you can eliminate the problem, and a full table scan, like below.
Whiile this makes your initial q moot for this specific example, it could naturally still be good to know just in general.
INSERT INTO T(...)
SELECT t.a * cj.modifier, t.b, 1
FROM sometable t
CROSS JOIN (
SELECT 1 AS modifier UNION ALL
SELECT -1
) AS cj
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 14, 2013 at 12:33 pm
Thanks, that's what i suspected, but it's hard to get how SQL Server semantics are in this case.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply