SQL Server: Performance Insert Into vs Select Into I am hearing different things from colleagues/research. What are good guidelines in performance for, Select Into vs Insert into when creating a temp table? I know difference is minimal for small tables. 

Eg: Table has 20 columns, 50 million rows.

I've had DBAs state, Insert into is faster, since compiler/parser, does not need to find Column data types on the fly. Others stating Select into is faster.
We conducted performance testing, and seems select into is slightly faster.

What are good principles in figuring which is faster and why? I would think Microsoft would optimize to make insert into , just as fast, for careful programming.

Article states following.

SQL Server Performance of SELECT INTO vs INSERT INTO for temporary tables


  The INSERT...INTO command will reuse data pages which are created in
  cache for insert/update/delete operations. It will also truncate the
  table when it is dropped. The SELECT...INTO command will create new
  pages for table creation similar to regular tables and will physically
  remove them when the temporary table is dropped.


Question is, why wouldn't Microsoft optimize to make insert into as fast as select into?

We have over 500 stored procedures to write for data warehouse, and require good guidelines for temp usage.

This article does not really focus on performance and reasons:

INSERT INTO vs SELECT INTO

Person in article mentioned good point:


  that's mostly because SQL Server knows that there is no contention for
  the destination table. The performance for insert into #temp
  with(tablock) select * from .. is roughly the same as the performance
  for select * into #temp from
What are good principles in figuring which is faster and why? I would
  think Microsoft would optimize to make insert into , just as fast, for
  careful programming.


The principles that I try to follow when analyzing something like this question are:


Avoid making unnecessary assumptions.
Read the official documentation.
Test the workload. The amount of testing depends on how fast I need the code to be.


I'm aware of two pieces of documentation that address your question. The first one is a blog post saying that SELECT INTO for temp tables has different behavior for eager writes as of SQL Server 2014. That is by design. So I don't think that it's correct to say that the difference is minimal for small tables. If anything, the optimization described in the blog post seems designed for smaller tables:


  The change in SQL Server 2014 is to relax the need to flush these
  pages, as quickly, to the TEMPDB data files.  When doing a select into
  … #tmp … or create index WITH SORT IN TEMPDB the SQL Server now
  recognizes this may be a short lived operation.   The pages associated
  with such an operation may be created, loaded, queried and released in
  a very small window of time.
  
  For example:  You could have a stored procedure that runs in 8ms.  In
  that stored procedure you select into … #tmp … then use the #tmp and
  drop it as the stored procedure completes.
  
  Prior to the SQL Server 2014 change the select into may have written
  all the pages accumulated to disk.  The SQL Server 2014, eager write
  behavior, no longer forces these pages to disk as quickly as previous
  versions.   This behavior allows the pages to be stored in RAM (buffer
  pool), queried and the table dropped (removed from buffer pool and
  returned to free list) without ever going to disk as long memory is
  available.   By avoiding the physical I/O when possible the
  performance of the TEMPDB, bulk operation is significantly increased
  and it reduces the impact on the I/O path resources as well.


The second piece of documentation explains that parallel insert into temp tables with INSERT INTO ... SELECT is available without a TABLOCK hint in SQL Server 2016 but requires a TABLOCK hint in SP1 and in future versions.


  The issue is first fixed in SQL Server 2016 Service Pack 1 . After you
  apply SQL Server 2016 SP1, Parallel INSERTs in INSERT..SELECT to local
  temporary tables is disabled by default which reduces contention on
  PFS page and improves the overall performance for concurrent workload.
  If parallel INSERTs to local temporary tables is desired, users should
  use TABLOCK hint while inserting into local temporary table.


Going back to your original statement, you can't logically deduce which of the two will be faster. What is faster depends on how Microsoft designed the software and the characteristics of your workload. Making guesses about the amount of time needed to create the column definitions just isn't helpful. Testing is helpful. If your testing suggests that SELECT INTO is faster than go with that. For what it's worth, I also work on data warehouse loading with a close eye on performance and I haven't seen the difference between the two approaches be anything that's worth worrying about.