SQL Server 2005 and temporary table scope

by

Local temporary tables (start with #) are limited to your session; other sessions, even from the same user/connection string, can’t see them. The rules for the lifetime depend on whether the local temporary table was created in a stored procedure:

  • A local temporary table that is created in a stored procedure is dropped when the procedure ends; other stored procedures, or the calling process, can’t see them.
  • Other local temporary tables are dropped when the session ends.

Global temporary tables (start with ##) are shared between sessions. They are dropped when:

  • The session that created them ends
  • AND no other session is referring to them

This command can be handy to see which temporary tables exist:

select TABLE_NAME from tempdb.information_schema.tables

And this is handy to drop temporary tables if you’re not sure they exist:

if object_id('tempdb..#SoTest') is not null drop table #SoTest

See this MSDN article for more information.

More Related Contents:

  1. How do I UPDATE from a SELECT in SQL Server?
  2. How to return only the Date from a SQL Server DateTime datatype
  3. How to calculate age (in years) based on Date of Birth and getDate()
  4. CROSS JOIN vs INNER JOIN in SQL
  5. Referring to a Column Alias in a WHERE Clause
  6. Convert Month Number to Month Name Function in SQL
  7. Function vs. Stored Procedure in SQL Server
  8. Multiple INSERT statements vs. single INSERT with multiple VALUES
  9. Limit on the WHERE col IN (…) condition
  10. Removing leading zeroes from a field in a SQL statement
  11. converting Epoch timestamp to sql server(human readable format)
  12. Getting result of dynamic SQL into a variable for sql-server
  13. How to get difference between two rows for a column field?
  14. Better techniques for trimming leading zeros in SQL Server?
  15. How do I split a delimited string so I can access individual items?
  16. What’s the best way to select the minimum value from several columns?
  17. Counting DISTINCT over multiple columns
  18. SQL Server SELECT into existing table
  19. Why use Select Top 100 Percent?
  20. How to check if a stored procedure exists before creating it
  21. Correct use of transactions in SQL Server
  22. How do I prevent a database trigger from recursing?
  23. What is the Null Character literal in TSQL?
  24. Aggregate function in an SQL update query?
  25. SQL Server FOR XML Path make repeating nodes
  26. SQL Server SELECT INTO @variable?
  27. WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT
  28. Select a column if other column is null
  29. Date serial in SQL?
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment