Temporary Table:
Temporary tables are very similar to the permanent tables. Permanent tables are created in the database where you specify, and it remains in the database permanently until you delete (drop) them. And in the case of the temporary table get created in TempDB and are automatically deleted, when they are no longer used.
Different Types of Temporary tables:
1. Local Temporary Tables.
2. Global Temporary tables.
1. Local Temporary Tables
A local temporary table is available, only for the connection that has created the
table.
A Local temporary table is automatically dropped. When the connection that has
created
Then it, is closed.
To create a Local Temporary table, prefix the name of the table with 1 pound (#)
symbols.
CREATE TABLE #EmployeeDetail(Id int, Name nvarchar(20))
Inserting Data into the temporary table:
Insert into # EmployeeDetails Values(1, ‘Tidda’)
Insert into # EmployeeDetails Values(2, 'Bolero')
Insert into # EmployeeDetails Values(3, 'Vilaa')
Select the data from the temporary table:
Select * from # EmployeeDetails
If user are want to delete or Drop the Temp table then use:
DROP TABLE #EmployeeDetails
It is also possible for different connections, to create a local temporary table with
the
Same name.
For example: UserA and UserB both can create a local temporary table with the
same
Name #UserDetails.
Check if the Local temporary table is created:
You can check the existence of temporary tables using object explorer like:
Note: If you want to create a Temporary table inside the body of Stored procedure then
it is automatically destroyed after execution of stored procedure.
2. Global Temporary tables
When you want to create Global temporary table then you use 2 pounds (##) symbols
front of the table name.
Create Table ##EmployeeDetails(Id int, Name nvarchar(20))
Global temporary tables are visible to all the connection of the SQL Server, and are only
Destroyed when the last connection or last Query window is closed.
Multiple users on multiple connections can have local temporary table with the same
Name, but, a Global temporary table name has to be unique, and if you inspect the name
Of the global temp table, in the object explorer not add any random numbers at end of the
Table name.
Difference between Local and Global Temporary Tables:
1. Local temp tables are created with the help of single pound (#) symbol, whereas Global temp tables are prefixed with 2 pound (##) symbols.
2. SQL Server add some random numbers end of the local temp table name, where Global temp table not add any random numbers end of the name.
3. Local temp table are only visible to that query window of the SQL Server which has created it, whereas global temporary tables are visible to all the SQL server sessions.
4. A Local temporary table is automatically dropped. When the connection that has created then it, is closed, where Global temporary tables are only destroyed when the last connection or last Query window is closed.
Leave Comment