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 #
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
- We can't use local temporary table inside a SQL Function.
- We can't create trigger on a temporary tables.
- We can't create View on the temporary tables.
