T-Sql Algorithm Question

Question

Hello,

I have a T-Sql Statement as follows;

Insert into Table1
Select * From Table2

I want to know the running sequence. Does the insert waits select statement to finish before starting or it starts asap select statement starts returning values and expects new records from the select statement to continue.

This is a plain stored procedure and no transactions used.

Answer 1

What you have there is effectively a single statement. It will only insert into Table2 the records that were present in Table2 when you begin the insert. Otherwise the properties of ACID would not apply and you'd have issues with isolation (what if the sp is run twice concurrently) and durability. SQL Server will enforce this via locking.

Answer 2

To echo @CodeByMoonlight's answer, and to address your comment there: the physical considerations (including the specifics of locking) are always subordinate to the logical instructions specified by the query.

In the processing of an INSERT ... SELECT statement, logically speaking the SELECT is carried out to produce a resultset, and then the rows of this resultset are INSERTed. The fact that in this case the source table and the target table are the same table is irrelevant. I'm fairly sure that specifying NOLOCK or TABLOCK would in any case apply only to the SELECT, if that's where you position them.

Consider as another example this statement, which makes no sense if you read it in an 'imperative' way:

UPDATE SomeTable
SET Column1 = Column2, Column2 = Column1

With an imperative, rather than set-based, understanding, this statement might look as if it will result in Column1 and Column2 having the same value for all rows. But it doesn't - it in fact swaps the values in Column1 and Column2. Only by understanding that the logical instructions of the query dictate what actually happens can this be seen.