Few days ago, I had chat with few SQL Server Database Developers, they asked me how ‘Select Into’ bulk operation works in SQL Server and whether it is ‘Batch Mode’ operation or not?. In this post I will write my findings from ‘Select Into’ bulk operation in SQL Server.
Basically, bulk operation comes with minimal log in transaction log file, which makes it much faster compare to ordinary OLTP transactions due to SQL Server behavior towards transactions. ‘Select Into’ bulk operation reads every single rows from the source table and inserts all the rows one by one in the target table, the only difference is that bulk operation always logs transactions at page level.
Below figure shows the transaction log records after ordinary OLTP insert transaction, as you see before and after every insert operation, there is ‘LOP_BEGIN_XACT’ and ‘LOP_COMMIT_XACT’ operations.
Now it is time to take a look at transaction log records after ‘Select Into’ bulk operation.
As you observe from above figure, there is not any ‘LOP_BEGIN_XACT’, ‘LOP_INSERT_ROWS’ and ‘LOP_COMMIT_XACT’ operations, instead we have ‘LOP_MODIFY_ROW’ on context of ‘LCX_PFS’, ‘LOP_SET_BITS’ on context of ‘LCX_GAM’ and ‘LCX_IAM’.
As I mentioned before ‘Select Into’ reads all the records from the source table one by one and inserts into the target table one by one as well, instead it only logs page level transactions. that’s why we see a lot of ‘LCX_PFS’,’LCX_IAM’ and ‘LCX_GAM’. You can confirm the Row Execution Mode from execution plan.
Author: Hamid Jabarpour Fard