Are there any performance differences between using an explicit create table statement and loading data versus selecting into. This example just shows 2 columns, but the question is geared towards using very large tables. The example below also uses temporary tables, though I'm wondering the effects upon using regular tables as well. I think they would be the same regardless of table type though.
Temp table scenario:
--- Explicitly creating temp table first and then loading.
create table #test1 (id int, name varchar(100))
insert into #test1 (id, name) select id, name from #bigTable
--- Creating temp table by selecting into.
select id,name into #test2 from #bigTable
or regular tables:
--- Explicitly creating table first and then loading.
create table test1 (id int, name varchar(100))
insert into test1 (id, name) select id, name from #bigTable
--- Creating table by selecting into.
select id,name into test2 from bigTable
What are everyone's thoughts on this? I think that explicitly creating the table and loading must have better performance than selecting into as select into must evaluate the expressions within the statement in order to create a table.
Our organization usually creates temp tables explicitly as a standard practice, and we're wondering what everything thinks is actually the best practice.