1

If I have a stored procedure that users are running constantly that creates temp tables and don't clear these temp tables at the end of the stored procedure, do these temp tables continue to exist and use up memory after the execution of the SP?

I tagged SQL Server 2008 because this is the one we are currently using and 2019 is the version we are upgrading to soon. I'm wondering if this question has a different answer in these two versions.

TheMortiestMorty
  • 421
  • 2
  • 4
  • 12
  • 1
    Yes, and no... In recent versions of SQL Server the temporary tables can actually be persisted for performance reasons (there's a list of caveats on when this happens), but they don't contain any data. As for memory, that is a *completely* different matter; SQL Server doesn't release memory to the OS after it's used it, but that doesn't mean that the temporary table remains in that memory. A temporary table (from the perspective of being accessible) only persists for as long as the scope that created it does; for an SP that's when the procedure's execution ends. – Thom A Feb 23 '23 at 16:53
  • Note that whether or not user "clear" the temp table (deleting, truncating or dropping), when the session ends, the temp table goes poof. If *creating* the table was done in the stored procedure, then it goes poof at the end of the sproc, whether or not it's explicitly dropped. – Jeroen Mostert Feb 23 '23 at 16:57
  • We've used the same technique as you through sql 2000 => 2019 and i doubt it has changed since, you dont' need to drop temp tables in any normal usage – siggemannen Feb 23 '23 at 17:00
  • 1
    In reference to my comment: [SQL Server Temporary Object Caching](https://sqlperformance.com/2017/05/sql-performance/sql-server-temporary-object-caching) – Thom A Feb 23 '23 at 17:05
  • I made it a habit to drop temp tables at the beginning and at the end of all of my SPs for the past 4 years...what a waste of time lol – TheMortiestMorty Feb 23 '23 at 17:06
  • Yes, `DROP`ing them at the start or end is effectively pointless, @whatin1992 . Note, however, that if a temporary object of the same name is created before the execution of the procedure, that will cause problems. As such it's a good habit to give your temporary objects a meaningful name, that (hopefully) won't result in a clash. Something like `#temp` would be a *poor* choice, for example. Note in this [db<>fiddle](https://dbfiddle.uk/qK0sAs8n) that in the batch where I `EXEC` twice, only 1 row in each dataset is returned, not 2 in the latter, and the whole thing fails ihe former batch. – Thom A Feb 23 '23 at 17:14
  • If you end up running the stored proc code outside the context of the stored proc whilst developing/debugging then having the `DROP` statements can be useful though. – Martin Smith Feb 23 '23 at 17:23
  • It doesn't do any harm to drop the temp table at the end of a proc, or drop if exists (it is, or at least *was* the recommended practice), however just be aware what goes on behind the scenes is not the same as dropping a normal user table as per the caching of meta data described above. – Stu Feb 23 '23 at 17:38
  • @Larnu about your note - does that apply to 2 different sessions creating a temp table with the same name? Let's say in Report A and Report B I use a SP that creates `#temp` with wildly different schemas. If two different users were to each run one of these reports, could there be a conflict because of the same temp table name? Or is SQL Server smart enough to apply some level of scoping to prevent this? – TheMortiestMorty Feb 23 '23 at 20:09
  • Temporary tables are still scoped to the scope they are in @whatin1992 . 2 connections can both create a temporary table with the same name; they are different scopes. – Thom A Feb 23 '23 at 20:10
  • @Larnu phew! That's a relief. Thanks for the quick response! – TheMortiestMorty Feb 23 '23 at 20:11
  • 1
    One caveat to be aware of is that while temporary tables themselves aren't accessible beyond the scope that created them SQL Server will use a deferred drop if their data is less than around 8MB - this can catch you out due to the table's statistics still being cached. If you regularly recreate that same temporary tables that include indexes and/or unique constraints and/or primary key constraints you should execute `UPDATE STATISTICS` against them after populating them otherwise the statistics cached from previous use can mislead query planner in subsequent queries within the current scope. – AlwaysLearning Feb 23 '23 at 21:51

0 Answers0