INSERT INTO vs SELECT INTO

Introduction

What is faster to use…

INSERT INTO or SELECT INTO?

I have read many blog posts comparing INSERT INTO to SELECT INTO, yet so many still get it wrong. In this post, I will provide the full picture of performance differences between INSERT INTO and SELECT INTO and how to use it correctly.

Let’s Get Dirty

As this comparison takes place across four versions of SQL Server, namely 2012, 2014, 2016 & 2017. I will keep this post short. Therefore, if you are interested in the details of the scripts used & more in these tests, see this post:  Get Dirtier Here.

Each test was conducted 3 times where the average was used for the conclusion of these tests.

Tests Performed:

  • INSERT INTO without TABLOCK
  • INSERT INTO with TABLOCK
  • INSERT INTO with OPTION (MAXDOP 1)
  • SELECT INTO with OPTION (MAXDOP 1)
  • SELECT INTO without OPTION (MAXDOP 1)

Conclusion

The below table shows a comparison of execution time (ms) between INSERT INTO & SELECT INTO across the versions of SQL Server. Orange depicts single threaded executions and green depicts parallelly treaded executions.

 
What did we learn?
  • SQL Server 2012 does not support parallel “Table Insert” executions;
  • SQL Server 2014 SELECT INTO is faster than INSERT INTO by default as SELECT INTO is the only “Table Insert” operator that can run parallelly by default;
  • SQL Server 2016 introduced parallelism for the INSERT INTO “Table Insert” operator, making it just as fast as SELECT INTO;
  • SQL Server 2016/2017 INSERT INTO will only run parallelly if you use the TABLOCK hint;

In the end, we can conclude that SELECT INTO and INSERT INTO have equal performance, except in SQL Server 2014.

You may also like...

1 Response

  1. Mfanimpela says:

    Nice test! How sure are we that in all times the conditions of the environment were/remained the same? As one executes SQL scripts the performance of the CPU, RAM, SQL Instance in terms of threads and so forth will either have a diminishing return (depending on the kind of data being in question) or a steady upward return (as the engine is ‘AWARE’ of current process/thread): in essence there are many factors influencing this. Even if the Test was run of similar but separate machines, on we would still need to consider other factors.

Leave a Reply

Your email address will not be published. Required fields are marked *