Types of Temporary Tables
SQL Server provides two types of temp tables based on the behavior and scope of the table. These are:
- Local Temp Table
- Global Temp Table
Local Temp Table(“#”)
Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash (“#”) sign.
Global Temp Table(“##”)
Global Temporary tables name starts with a double hash (“##”). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.
Creating Local Temporary Table:
CREATE TABLE #temp_tblEmployee ( [Emp_ID] [int] NOT NULL, [Emp_Name] [varchar](max) NOT NULL ) INSERT INTO #temp_tblEmployee SELECT 1,'Shashangka Shekhar' GO SELECT * FROM #temp_tblEmployee
Creating Global Temporary Table:
CREATE TABLE ##temp_tblEmployee ( [Emp_ID] [int] NOT NULL, [Emp_Name] [varchar](max) NOT NULL ) INSERT INTO ##temp_tblEmployee SELECT 1,'Shashangka Shekhar' GO SELECT * FROM ##temp_tblEmployee
When to Use Temporary Tables?
- When we are doing large number of row manipulation in stored procedures.
- This is useful to replace the cursor. We can store the result set data into a temp table, then we can manipulate the data from there.
- When we are having a complex join operation.