SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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 name] (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
   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

Create procedure insert_LTT
  Insert into  #employee ( empFname  ,  empEname  , empdate )
  Values ( 'Vibodh','Johari',getdate())

Transactions are also applicable in local temporary tables.

Alter Procedure test_LTT
Begin Transaction
   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

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.

  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.

Vivek's SQL Notes

Vivek johari is currently a Analyst and have more that 5.5 yeras of experience in database. He has Master degree in Computer and also he is Microsoft certified Sql DBA (MCTS)& Microsoft certified SQl BI professional(MCTS). He is also Oracle certified profession(OCP)DBA in ORACLE 10g and ORACLE 9i.He has the experience of working in PL/SQL, T-SQL and SSIS/SSRS. His work basically involved designing and optimization of the Database.He has also published many database articles on his blog Technologies with Vivek Johari.


Leave a comment on the original post [feedproxy.google.com, opens in a new window]

Loading comments...