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