MS SQL Server: Do multiple queries in a batch ever execute in parallel and if so what happens when the second query is dependent on the first?
Batch statements are only ever executed serially in the order they appear in the batch.
Now, if you have two statements sent to the server by two different batches, they will run independently and essentially simultaneously (locking and latching aside).
Take for example the following code:
CREATE TABLE #t
(
i int
);
INSERT INTO #t (i) VALUES (0);
The CREATE TABLE
always runs prior to the INSERT INTO
statement. Consider this:
SELECT 1;
SELECT 2;
The above code will always be ran in order, i.e. SELECT 1
runs first, then after it completes, SELECT 2
runs.
There are multiple ways independent batches can be ran simultaneously, including the use of Multiple Active Result Sets, or MARS, however none of those affect the serial processing of statements within a single batch.
From comments:
The answer "no" is not specific to SQL Server. Any relational database system that respects the rules of ACID is required to execute statements in one batch sequentially, or at least behave as if they do. In practice, it would take a very clever engine indeed to parallelize a batch (as opposed to an individual statement) without this being noticeable, but if it existed, your hypothetical second scenario would be no cause for worry either -- the rules would forbid the first statement from seeing the results of the second. - jeroen-mostert