Does ‘Select Into’ is Batch Mode in SQL Server?

The estimated reading time for this post is 1 minutes

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.

PFS2

Now it is time to take a look at transaction log records after ‘Select Into’ bulk operation.

PFS3

 

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

PFS4

 

PFS5  PFS6

 

 

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz