TEMP TABLE & GLOBAL TEMP TABLE & TABLE VARIABLE

Local Temporary Tables

These tables are created within a procedure and stored in the temp_db provided by

SQL server. They can be identified with a session specific identifier. It can be used when data is coming from another stored procedure. It also reduces the amount of locking required and also involves less logging. Still, there are few limitations such as character limit is 116 and transactions can create unnecessary locks in temp_db.

Syntax:

Create table #Student (Id int, Name varchar(50))

Global Temporary Tables:

These tables are same as local temporary table but the difference lies in the lifetime as it is available for all the sessions. This can be useful when the same set of data is required by one or more users. But the issue will come when the user, who should not be given access to this data, will have access to it as it is a global table and available for all users.

Syntax:

Create table ##Student (Id int, Name varchar(50))

See the difference of two #.

Table Variables:

Same structure as a normal table but only difference is the shortest life time among all the varieties. This table is created and stored in memory and its lifetime is decided by the stored procedure who have created it. Once stored procedure/DML statement exits, this table gets auto cleaned and memory gets free. Apart from that, log activity is truncated immediately. An important note, If we have a requirement to use a table structure in user defined function then we have only one option as Table variable and no other variety can be used.

Syntax:

Declare @Student Table (Id int, Name varchar(50))