is it necessary to drop temp table in stored proc
in [Programming SQL]
|
Prev: Que Assignments
Next: year week table help
From: Andrew J. Kelly on 17 Apr 2010 17:16 Explicitly dropping temp tables can have a negative effect in that you will not be able to use the built in caching feature in the later versions of SQL Server. Drop it only if it needs to be to prevent an error based on your code requirements, otherwise let SQL Server drop it. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "rodchar" <rodchar(a)discussions.microsoft.com> wrote in message news:92DC01B2-674F-479A-BF26-C6BCDED4735E(a)microsoft.com... > Hi All, > > IF object_id('tempdb..#TEMP_CORE_DATA') IS NOT NULL > BEGIN > DROP TABLE #TEMP_CORE_DATA > END > > Is the preceding sql necessary when the temp table is put in a stored > procedure? > > thanks, > rodchar
From: Dan Holmes on 19 Apr 2010 07:57 On 4/17/2010 5:16 PM, Andrew J. Kelly wrote: > Explicitly dropping temp tables can have a negative effect in that you > will not be able to use the built in caching feature in the later > versions of SQL Server. Drop it only if it needs to be to prevent an > error based on your code requirements, otherwise let SQL Server drop it. > Where do i find more documentation on that? I have never heard this before.
From: Gert-Jan Strik on 19 Apr 2010 14:36
Dan Holmes wrote: > > On 4/17/2010 5:16 PM, Andrew J. Kelly wrote: > > Explicitly dropping temp tables can have a negative effect in that you > > will not be able to use the built in caching feature in the later > > versions of SQL Server. Drop it only if it needs to be to prevent an > > error based on your code requirements, otherwise let SQL Server drop it. > > > Where do i find more documentation on that? I have never heard this before. If had never heart about that either. We are never too old to learn. I stumbled upon this article: http://www.sqlmag.com/article/sql-server/caching-of-temporary-objects.aspx -- Gert-Jan |