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.
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:
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