Local and global temporary tables in SQL Server

by

  • Table variables (DECLARE @t TABLE) are visible only to the connection that creates it, and are deleted when the batch or stored procedure ends.

  • Local temporary tables (CREATE TABLE #t) are visible only to the
    connection that creates it, and are deleted when the connection is
    closed.

  • Global temporary tables (CREATE TABLE ##t) are visible to everyone,
    and are deleted when all connections that have referenced them have closed.

  • Tempdb permanent tables (USE tempdb CREATE TABLE t) are visible to
    everyone, and are deleted when the server is restarted.

More Related Contents:

  1. What’s the difference between a temp table and table variable in SQL Server?
  2. When should I use a table variable vs temporary table in sql server?
  3. Which are more performant, CTE or temporary tables?
  4. EF4 – The selected stored procedure returns no columns
  5. T-SQL Dynamic SQL and Temp Tables
  6. Check if a temporary table exists and delete if it exists before creating a temporary table
  7. SQL Server – error when creating stored procedure
  8. What are the main performance differences between varchar and nvarchar SQL Server data types?
  9. SQL Server String or binary data would be truncated
  10. How to change identity column values programmatically?
  11. SQL Server Regular expressions in T-SQL
  12. Concatenating Column Values into a Comma-Separated List
  13. List the queries running on SQL Server
  14. SQL Server pivot vs. multiple join
  15. SQL Server Bulk insert of CSV file with inconsistent quotes
  16. Real vs. Floating Point vs. Money
  17. SQL Server Query: Fast with Literal but Slow with Variable
  18. How can I make HTTP request from SQL server?
  19. SQL Server, the misleading XLOCK & optimizations
  20. Database design: Calculating the Account Balance
  21. migrate data from MS SQL to PostgreSQL?
  22. “Prevent saving changes that require the table to be re-created” negative effects
  23. How to change column datatype in SQL Server database without losing data?
  24. Convert UTC Milliseconds to DATETIME in SQL server
  25. freeTDS not using its config
  26. @@ERROR and/or TRY – CATCH
  27. replace NULL values with latest non-NULL value in resultset series (SQL Server 2008 R2)
  28. How to make a passthrough / passthru query editable?
  29. To run a SSIS package outside of SQL Server Data Tools you must install Move File to Archive of Integration Services or higher
  30. Tool for Scripting Table Data

Leave a Comment