SQL Server provides the feature temporary table which helps user to replace Stored Procedure that requires large number of row manipulation and to replace complex SQL Join operations. The temp table can be created at runtime and can do all kind of operations like normal table. But, as its name implies, the scope is limited.
SQL Server provides two types of temp tables based on its connection life time.
Local Temp Table
Local temp tables are only available to the current connection for the user, and are deleted when the connection is closed. it requires the single hash value “#” as the prefix when created.
Syntax: (CREATE TABLE #MyTempTable).
--Create Local Temp Table CREATE TABLE #UserTempTable( UserID int, UserName varchar(250), Email varchar(250)) --Insert rows into Local Temp Table Insert into #UserTempTable values(1,'Morgan','[email protected]') Insert into #UserTempTable values(2,'Tim','[email protected]') Insert into #UserTempTable values(2,'Shiv','[email protected]') --Select rows from Local Temp Table Select * from #UserTempTable
Global Temp Table
Global Temp tables are visible to all connections of SQLServer, and only destroyed when the last connection referencing the table is closed. it requires the double hash value “##” as the prefix when created.
Syntax: (CREATE TABLE ##MyTempTable).
--Create Global Temp Table CREATE TABLE ##UserTempTable( UserID int, UserName varchar(250), Email varchar(250)) --Insert rows into Global Temp Table Insert into ##UserTempTable values(1,'Morgan','[email protected]') Insert into ##UserTempTable values(2,'Tim','[email protected]') Insert into ##UserTempTable values(2,'Shiv','[email protected]') --Select rows from Global Temp Table Select * from ##UserTempTable
Both Temporary tables are created in tempdb. Whenever we create new temp table, we can see it under Temporary folder of tempdb database.
Thanks,
Morgan
Software Developer
Advertisement