SELECT… INTO | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SELECT… INTO

How costly are SELECT… INTO #Table queries in comparison to defining a tables definition first, then populating it using INSERT INTO… SELECT… ? If this is used in SP’s, will it cause them to recompile at each occurance of SELECT… INTO ? Thanks in advance,
Carl.
I think the main concern with SELECT … INTO #Table is when it is done within a transaction, because the transaction will then also cover tempdb (where your #Table is created) and this may well cause locking and blocking issues. The preferred way would be to create #Table first, then start the transaction, and then use the regular INSERT INTO #Table (…) SELECT … syntax. Sorry, no idea if it would cause a recompile.
Hi ya, yes if select into #table is used in a proc then it will cause a recompile, since you’re creating a new table. I’d say that best practice is to create the temp tables explicitly at the start of the proc Cheers
Twan
Twan, If the SP is recompiled "since you’re creating a new table", then wouldn’t the SP be recompiled also if the SP has a CREATE TABLE #Table …… statement?
We have it as part of our standards here to never perform a SELECT…INTO #… The reasons have been stated here. They cause recompiles. They also cause locking issues in tempdb. Better to just go the extra mile now and save grief later. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Thanks guys, just what I was looking for.
Hi Adriaan, yes using a create #table will also cause a recompile unless it is before any normal sql statement in the proc. Hence the recommendation that temp tables are always created at the very start of a stored proc Cheers
Twan
Hi Twan, That makes sense. Thanks for clearing it up! Adriaan
Bear in mind, that if you are creating temp tables inside a stored procedure, then one of the main problems with SELECT INTO can be reproduced by using the following code: INSERT INTO mytable
exec mystoredprocedure The entire INSERT statement will run inside a transaction, so if your stored procedure creates a temp table, those locks on the system tables will be held until the transaction completes. This can reduce your system to effectively only allow one user at a time to perform the query above. Dave Hilditch.
HI Dave,<br /><br />yes although this is only if you’ve started a transaction beforehand, which would be the same if you’d created a temporary table after starting a transaction. If you haven’t then the statement will commit when it completes…<br /><br />in any case temp tables, while useful, present some unique challenges to developers and dbas alike <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /><br /><br />Cheers<br />Twan<br /><br />
]]>