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.

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:-

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_LTTAsBegin CREATE table #employee (id int identity(1,1), empFname nvarchar(100), empEname nvarchar(100), empdate datetime) Exec insert_LTT select * from #employee Drop table #employeeEnd

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.