What are the performance variations between utilizing an explicit create table statement and loading data versus choosing into. This situation just shows 2 posts, but now you ask , targeted towards using large tables. The example below also uses temporary tables, though I am wondering the results upon using regular tables too. I believe they will be the same no matter 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 exactly are everyone's ideas about this? I believe that clearly creating the table and loading should have better performance than choosing into as choose into must assess the expressions inside the statement to be able to produce a table.
Our company usually produces temp tables clearly like a standard practice, and we are wondering what everything thinks is really the very best practice.
Choose into has logging benefits (does not do just as much), therefore the performance is really better generally. However, it errors when the table is available, and does not build items like indexes or constraints, just posts.
Is dependent the thing you need it for. I understand we've got some actions that
SELECT ... INTO then relabel since it is faster than upgrading that old table (clearly with many different fluff around to rebuild table objects etc).
Keep in mind our usage is not with temp tables, which I have just observed inside your real question is the situation.
Within the situation of tables with indexes, place into will need to keep up with the indexes included in the place process. You will find then other table objects that induce more processing, for example triggers. Within the situation of choose into, the table is bare-bones, to date as I am aware, so initial place performance is excellent. Plus transaction log impact is minimal (mentions this for the reason that link in your question).
It truly is dependent around the usage, for temporary tables, I'd assume they will be relatively short-resided, so choose into then a truncate/drop can function well. Should they have longer spans, but they are otherwise throw-away, again choose into then an eventual drop can function.
If they have to live lengthy after being produced and therefore are not throw-away, then apart from the first creation and place of information (which is fast) you will subsequently be back and where you started when it comes to subsequent card inserts - you'd be best placed just tuning the table to simply accept fast card inserts, for instance by getting minimal indexes or by crippling indexes prior and re-enabling publish place.
Within the situation of huge tables that have clustered indexes, I have also seen a trick where data being placed is purchased through the clustered index on place.
CREATE TABLE provides you with a much better treatments for your table's definition just before placing the information, like
NOT NULL, constraints, etc. stuff that you can't do using
SELECT INTO is really a minimally drenched operation, but
INSERT..SELECT may also be minimally drenched, at some conditions.
See The Data Loading Performance Guide, particularly the section: Outlining Minimal Logging Conditions.
Briefly, if you don't worry about the restrictions, etc. (e.g. you need to rapidly produce a copy of the table) the benefit of
SELECT..INTO IMHO is really a shorter code .
Otherwise, you need to use another way, and also you would still have the ability to get it minimally drenched.