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))
No comments:
Post a Comment