Wednesday, 28 March 2012

Temporary table and Table variable



In this article I am trying to collects some facts related to temporary table and table variable. So let's start about it.


The concepts of the temporary tables, which help developer a great, relax to maintain T-SQL statements. This table is created on run time and can do the all kind of operations that normal table can do. But based on the type of table the scope is limited.

The temp tables are created in the tempdb database. So before starting about temp table, let's take a quick look about tempdb database.


Tempdb database


It is a system database global resource that is available to all users. Some facts are mentioned bellow.


1.     Temporary use objects that are explicitly created such as, global and local   

temporary tables, temporary  stored procedure, table variables or cursors.

2.     Internal objects created by SQL Server database engine. For example work  

tables to store intermediate  result for spools or sorting.

3.     Row versions that re generated by data modifications transactions in a database

that use read-committed using row versioning isolation or snapshot isolation transaction.

4.     Row version that are generated by data modification transactions for feature.



Type of temporary table


Based on behavior and scope the temporary table is divided into two categories.


     1.       Local Temp Table

     2.       Global Temp Table


Local temp table


Local temp tables are only available to the current connection for the user and automatically deleted when the user disconnects from instances. It is started with # sign.




CREATE TABLE #tbl_localTemp

             (sRoll  Int,

              sName  Varchar(50),

              sClass Int)



Global Temp table


Once the table has been created by a connection, like a permanent table it then available to any user by any connection.  It is only be deleted when all connections have been closed. Global temp table name starts with ##.




CREATE TABLE ##tbl_localTemp

             (sRoll  Int,

              sName  Varchar(50),

              sClass Int)



Some points we must remember related to temporary table



1.     As they are created in a separate database named tempdb, so additional overhead and can causes performance issue.

2.     Number of rows and columns need to be minimised as we needed.



Table variable


Alternate of the temporary table is table variable. Which can do all kinds of operation we can do with the temporary table.  It is always useful for less data. If result set returns large amount of data we use the temporary table.




DECLARE @tblvrbl_local  TABLE

        (sRoll  Int,

         sName  Varchar(50),

         sClass Int)



Difference between Temp table and Table Variable


The main differences are mentioned bellow.


1.       Table variable are transaction neutral. They are variables and are not bound to a transaction

2.       Temp table behave same as normal table and are bound by transaction.


Hope you like it. 


Posted by: MR. JOYDEEP DAS





  1. we can not create index on table variable unlike temporary table.

    1. DECLARE @tblvrbl_local TABLE
      (sRoll Int primary key,
      sName Varchar(50),
      sClass Int)

      when you create a primary key you get an index

  2. Why cant we use table variable for large data or datasets? Any good reason? And if there is a limit then what is the max number of size (in bytes) which table variable supports?

  3. thanks for sharing this valuable post, you said this table can be created during the run-time... actually i have need to create viz this format
    DOT NET Training Institutes in Chennai


  4. Nice blog, here I had an opportunity to learn something new in my interested domain. I have an expectation about your future post so please keep updates.
    SAP PP Training In Chennai

  5. Thanks for your wonderful post.It is really very helpful for us and I have gathered some important information from this blog.If anyone wants to get Dot Net Training Chennai reach FITA, rated as No.1 Dot Net Training Institutes in Chennai.

  6. Thanks for sharing this valuable information to our vision.
    ccna course in Chennai

  7. Excellent post!!! The future of .net application development is on positive note. It offers huge career prospects for talented professionals all over the world. Training on .net technology will ensure good salary package. Best DOT NET Training institute in Chennai | DOT NET Training

  8. Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing.Angular training in chennai


  9. Your blog is really awesome. Thank you for your sharing this informative blog. Recently I did PHP course at a leading academy. If you are looking for best PHP Training Institute in Chennai visit FITA IT training academy which offer real time PHP Training in Chennai.

  10. Nice article i was really impressed by seeing this article, it was very interesting and it is very useful for me..I get a lot of great information from this blog. Thank you for your sharing this informative blog. Mysql Training in chennai | Mysql Training chennai | Mysql course in chennai | Mysql course chennai

  11. Paris airport transfer - Parisairportransfer is very common in Paris that provides facilities to both the businessmen and the tourists. We provide airport transfers from London to any airport in London and also cruise transfer services at very affordable price to our valuable clients.

    Paris taxi
    Paris airport shuttle
    paris hotel transfer
    paris airport transfer
    paris shuttle
    paris car service
    paris airport service
    disneyland paris transfer
    paris airport transportation
    beauvais airport transfer
    taxi beauvais airport
    taxi cdg airport
    taxi orly airport