CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlsql-server-2005sql-server-2008-r2
user1254579
user1254579

Reputation: 4009

Dml statements in SP runs parallely or sequencially sql server?

The stored procedure contains 2 dml statements,ie 2 update queries. There is a need of executing the 2nd statement only after the complete exection of first query.

CREATE PROCEDURE usp_pn
AS
BEGIN

Update [db1].dbo.[table1]
SET
    [date1] = DateDiff(MI,[Ar_DateTime],[Departure_DateTime])



    Update [db1].dbo.[table1]
SET
    [InMinutes] = [date1]+some_calculation



END
GO

here i want to ensure the 2nd update has to run only after the first update completed So can i write the stored procedure as shown above or is there any modification required??# Please sugest

I just want to know the execution details:that is there are more than one dml statements in an sp then are these 2 going to run parallely or run one after another..

It might be a basic question but just want to know some thoughts... Thanks

Upvotes: 1

Views: 196

Answers (1)

Martin Smith
Martin Smith

Reputation: 453608

The statements always run sequentially.

Individual statements can be parallelised but SQL Server will never run different statements in the same batch in parallel.

A great Remus Rusanu article discussing this (and more) is Understanding how SQL Server executes a query

This should settle the often asked question whether statements in a SQL batch (=> request => task => worker) can execute in parallel: no, as they are executed on a single thread (=> worker) then each statement must complete before the next one starts.

For the specific example in your question though (perhaps this is an over simplified example?) I would use a single statement

UPDATE [db1].dbo.[table1]
SET    [date1] = DateDiff(MI, [Ar_DateTime], [Departure_DateTime]),
       [InMinutes] = DateDiff(MI, [Ar_DateTime], [Departure_DateTime]) + some_calculation

Upvotes: 2

Related Questions