SQL Server Performance Forum – Threads Archive
SELECT… INTOHow 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,
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, 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
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 />