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.

 

Example:

 

CREATE TABLE #tbl_localTemp

             (sRoll  Int,

              sName  Varchar(50),

              sClass Int)

GO

 

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 ##.

 

Example:

 

CREATE TABLE ##tbl_localTemp

             (sRoll  Int,

              sName  Varchar(50),

              sClass Int)

GO

 

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.

 

Example:

 

DECLARE @tblvrbl_local  TABLE

        (sRoll  Int,

         sName  Varchar(50),

         sClass Int)

GO

 

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

 

 

 

15 comments:

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

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

      when you create a primary key you get an index

      Delete
  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?

    ReplyDelete
  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

    ReplyDelete

  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

    ReplyDelete
  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.

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

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

    ReplyDelete


  8. 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.



    ReplyDelete
  9. 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

    ReplyDelete
  10. 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

    ReplyDelete