Home Forums SQL Server 2005 Administering Which one is better select * into # temp from tableA Vs create #temp table insert into... RE: Which one is better select * into # temp from tableA Vs create #temp table insert into...

  • June 21, 2011 at 5:58 pm

    #1342189

    opc.three (6/21/2011)

    Whether supplying an explicit column list or a * SELECT...INTO generates a table with no clustered index, i.e. a HEAP. If you're managing large quantities of data in temp tables and you want a clustered index on the table you may be better off creating the table ahead of time and then doing the insert.

    In either case, when the clustered index component mattered for the #tbl (not just the optimizer detecting better joining methods) due to data volume, I've almost always found better performance applying the clustered index after shoving all the data in.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA