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

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

 

 

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan