- Temporary Table in SQL Server are very similar to the permanent table. Permanent tables get created in the database you specify and remain in the database permanently, until you delete them. On other hand, Temporary tables get created in the temp DB and are automatically deleted, When they are no longer use.
- These tables are tables that exist temporarily on the SQL Server.
- The temp tables are useful for storing the immediate result sets that are accessed multiple times.
They are mainly divided into two types
- Local Temporary Table
- Global Temporary Table
Better to read the previous topic to get good understanding on the above points.
Stored Procedure in SQL
Function in SQL
Stored Procedure vs Function
Local Temporary Table
- Prefix the name of the table with one Hash (#)
- A local temp table is available, only for the connection that has created the table.
- A local temp table is automatically dropped, when the connection is closed.
- Query the sysobjects system table in TempDB. The name of table is suffixed with lot of underscores and a random number.
- It is possible for different connections, to create a local temp table with same name.
Way to create Temp Table
CREATE TABLE #TEMPTABLENAME (COLUMNNAME1 DATATYPE, COLUMNNAME2 DATATYPE, …)
SELECT */COLUMN NAME INTO #TEMPTABLENAME FROM TABLE_NAME
Global Temporary Table
- Prefix the name of the table with double Hash (##)
- Global temp table are visible to all the connection
- They are only destroyed when the last connection referencing table is closed.
- Query the sysobjects system table in TempDB. There will no random number suffixed of the table.
- There is no possible create a global temp table with same name
Way to create Temp Table
CREATE TABLE ##TEMPTABLENAME (COLUMNNAME1 DATATYPE, COLUMNNAME2 DATATYPE, …)
SELECT */COLUMN NAME INTO ##TEMPTABLENAME FROM TABLE_NAME
Please refer more details about Temporary Table
