What is the difference and similarities between local and global temporary tables?

Local Temporary tables

  • Visible only to the current session. Multiple users can’t use the same table.
  • To create these, table name should be prefixed with ‘#’

                Ex: CREATE TABLE dbo.#LocalTemp…..

  • Dropped automatically when the current session is closed

 Global Temporary tables

  • Visible to all the sessions. Any user can use the same table.
  • To create these, table name should be prefixed with ‘##’

                Ex: CREATE TABLE dbo.##GlobalTemp…..

  • Dropped automatically when the all connections are closed

 Similarities:

  • Stored in Tempdb database.
  • Since these are temporary, we can’t use in views, User defined functions and triggers.
  • We can’t GRANT or REVOKE permissions on temporary tables.

Leave a Reply

Your email address will not be published. Required fields are marked *