SQL Server – Local temporary table vs. Global temporary table

Temporary tables are a useful feature provided by SQL Server. Temporary tables created at runtime and can do all kinds of operations that one normal table can do. Because this is available at runtime, that’s why the scope of temporary tables is limited. These tables are created inside the tempdb database.

There are two types of Temporary tables.

• Local Temporary table
• Global Temporary table

Local temporary table: Local temporary tables are available to the current connection or sessions to the database for the current user and are dropped when the connection or session is closed. They are automatically deleted when connection or sessions closed. The name of local temporary table started with hash (i.e. #) sign.

Global temporary table: Global temporary tables are available to any connection or session once created, and is dropped when the last connection or session using it is closed. The name of global temporary table started with the double hash (i.e. ##) sign.

You can create the temporary tables in two different ways as

• Using Create Table
• Using select into

Example:

• Using CREATE
  

  CREATE TABLE #LocalTempTable1
  (
  ID int,
  Name varchar(20), 
  Address varchar(255)
  )

• Using SELECT…INTO

  SELECT name as UserName
  Address as UserAddress
  INTO #LocalTempTable1
  FROM Table1

Similarly, we can create the Global temporary tables by replacing single hash (i.e. #) with double hash (i.e. ##).

Reason to use temporary tables:

1. While doing a large number of row manipulations in a stored procedure.
2. This is useful to replace the cursor. (But this is not possible in every case)

Note: Temporary Tables need to be deleted when you are done with their work.