Creating a table using explicit create table statement versus select into

Question

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.

http://msdn.microsoft.com/en-us/library/ms188029.aspx

Answer 1

CREATE TABLE gives you a better control over your table's definition prior to inserting the data, like NOT NULL, constraints, etc. things that you cannot do using SELECT INTO.

SELECT INTO is a minimally logged operation, but INSERT..SELECT can also be minimally logged, at some conditions.
See The Data Loading Performance Guide, especially the section: Summarizing Minimal Logging Conditions.

Briefly, if you do not care about the constraints, etc. (e.g. you want to quickly create a copy of a table) the advantage of SELECT..INTO IMHO is a shorter code .
Otherwise, you should use the other way, and you would still be able to have it minimally logged.