SQL Server – Local temporary table

Local temporary table:- Local temporary table is created in the tempdb and it is visible to the current user’s session only. It remains exists till the current user session is connected. Once the user connection is disconnected it gets destroyed. Since the local temporary table is created in the tempdb, whenever we use temporary tables there is a interaction between the two database (tempdb and the database in which block of code is written) which may slow down the performance. We can use the temporary tables in the joins as well like physical tables. We can also use the temporary table with the While loop to replace the cursor. Temporary table is created with the prefix #. The syntax for the creation of local temporary table is given below:-

Create table #[table “” not found /]
(col1 datatype, col2 datatype……..coln datatype)

For example, below is the SQL command to create a temporary table #employee 

CREATE table #employee (id int identity(1,1), empFname nvarchar(100), empEname nvarchar(100), empdate datetime)

We can also defines constraints on the local temporary table. For example, the syntax to add primary key on the table #employee is given bellow:-

ALTER  Table #employee add constraint pk_LTT primary key (id)

We can also create indexes on the local temporary table also. For example

create index indx_LTT on #employee (empFname)

Insert command for temporary table is similar to insert command in the physical table. For example , if we want to insert data in the table # employee  we can use the following the SQL Statements:

Insert into  #employee ( empFname  ,  empEname  , empdate )
  Values ( ‘Vivek’,’ Johari’,getdate())

we can verify the creation of the local temporary table in the tempdb with the help of system view ‘sys.objects’.

SELECT * FROM sys.objects where type=’U’

Local temporary tables are itself dropped when the current user session is closed but it is better to drop it manually at the end of the block of the code in which it is defined or at the end of the stored procedure in which it is defined.
Create Procedure test_LTT
As
Begin
   CREATE table #employee (id int identity(1,1), empFname nvarchar(100),   empEname nvarchar(100), empdate datetime)
   Exec insert_LTT
   select * from #employee
   Drop table #employee
End

Create procedure insert_LTT
 as
begin
  Insert into  #employee ( empFname  ,  empEname  , empdate )
  Values ( ‘Vibodh’,’Johari’,getdate())
end

Transactions are also applicable in local temporary tables.

Alter Procedure test_LTT
As
Begin
Begin Transaction
   CREATE table #employee (id int identity(1,1), empFname nvarchar(100), empEname nvarchar(100), empdate datetime)
  EXEC Insert_LTT
Rollback
Select * from #employee
Drop table #employee
End

Since transactions are applicable in the Local temporary tables, the rollback command in the above procedure will rollback the entire transactions including the creation of the table #employee, execution of the above procedure will return the error that table #employee does not exists.

Limitations

  1. We can’t use local temporary table inside a SQL Function. 
  2. We can’t create trigger on a temporary tables.
  3. We can’t create View on the temporary tables.

DMCA.com

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in SQL Server, SQL Temporary Tables and tagged , . Bookmark the permalink.

Leave a Reply