Temp Tables in SQL Server

SELECT INTO is slow, horrendously slow on retrieving large recordsets.

Creating the table first, then doing and INSERT INTO is much faster, plus you have greater control over the table structure.

I think SELECT INTO trys to retrieve all data before creating the table and as the data doesn't have anywhere to go until the table is created it simply eats memory.  I could be wrong here.

I use temporary tables to store large results sets for reporting, usually where the values required cannot be calculated in a single SELECT statement. The reporting runs in a third party application with a scripting language which allows me to create and access the temporary tables. Doing this gave my application a huge performance boost (table hints also helped), and helped solved locking problems I was having due to the applications insistance on starting a transaction before running any script code.

However, I have one process which uses a stored procedure for speed, and have used a shared 'permanent' table because temporary tables go out of scope when the stored procedure finishes, as described in this quote from MS Books Online: 

"A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table"

I was wondering if a global temporary table would remain in scope after the procedure had completed, it is not clear from the explanation in Books Online.

Alternatively, I might be able to return a table datatype from the procedure, and create a local temporary table from that to drive the report.

Any suggestions welcome!

Just quick notes about the index for tmp sys table.

it's useless to create index just after tmp table creation. I add index after I fill table with data. Then the data are reindexed and all queries are faster after that.

Based on my experience I can say that using of tmp table is slower compare to use share table. Share table is more complicated with management, but faster. In my case, which is about this range (0-4000 rows and 9 columns(mostly varchar or uniqueidetifier)) I also use sys tmp table for related table to my share table, but there are only three columns. And finaly I use also table datatypes, but there is only one column and max 100 records.

I just don't know how I could solve problem with updating of statistics above this share table due to public role of logged dbuser.

This article should have been updated prior to re-publish as there are some errors and misconceptions contained within it. Sorry.

I agree with the last reply: This article should be rewritten taking into consideration the comments raised by those involved.

There is no explanantion to justify the statements regarding the limitations of temp tables.

We use a HUGE number of temp tables in our overnight batch jobs and we are constantly looking where temp tables can be replaced by shared permanent table objects. We also saw the benefit of creating indexes on the temp tables and moved the create index statements after the INSERT to improve performance. Does that apply to the CLUSTERED INDEX too? And should we update statistics following insert of a large number of rows?

Last question: Is it worth using TABLOCK on the temp table? It should be unique to that connection so in theory not required?

The article WITH the replies has been very useful. Thanks to all that contributed.

C

Yes you can

Or I sometimes declare it with the create table, e.g.

CREATE TABLE #mytable (mykey int PRIMARY KEY CLUSTERED, mycol char(1))

to let SQL make up the name