What are the major differences between Temporary Table and Variable Table
First of all, there is no universal rule for when and where to use
temp table or variable table. But using table variables for small
to medium volumes of data considered to be a rule of thumb.
One important thing - the table variable is NOT necessarily
memory resident. Under memory pressure, the pages
belonging to a table variable can be pushed out to tempdb.
Temp tables always stored in TempDB.
OK,lets see the main differences:
1) Table variables are out of scope of the transaction mechanism,
in other words the ROLLBACK is not going to work in case you
changed some data in variable table and rollback the changes.
Also table variables minimally logged and don't participate
in locking. This gives them some performance advantage due
to the fact that they don't require the overhead of locking
and logging.
2) A Stored Procedure containing a temp table will be
almost always recompiled. While s SP with table variable can
be statically compiled in advance.If you have a long SP that take
a while to compile you should consider whether to use the
temp tables.
3)Table variable can have Primary Key and Unique Constraints
defined. And the constraints can not be created in separate DDL
statements,only in table definition.table variable cannot have
regular indexes.The temp table can have all types of indexes.
4)Table variables are variables,this means that table variable
cannot be referenced by dynamic queries (you cannot 'see'
them in EXEC(string) statement) and in inner (sub) stored
procedures. One more limitation: table variables cannot be
used in an insert/exec (in SQL 2000) and 'Select into'
statements. Meaning you cannot insert SP`s result set into
table variable by using insert/exec method (in SQL 2000)
or use Select Into method:
insert into @table
exec sp_xxxxx
or
Select *
into @table
from SomeTable
*** The Insert/exec method can be used with table variables
in SQL 2005 and above.
5)Table variables don't have column statistics,temp tables
do have. In fact, the estimated row number in execution
plan for table variable is always 1. Without statistics,SQL Server
might choose a poor processing plan for a query that contains
a table variable.
6)Parallel query execution plans are not generated for queries
that modify table variables.They generated only for Select
statements that involve table variable(s).
7)In case of table variable the collations for the string
columns inherited from the collation of the current user
database,and in case of temp tables it will take the collation
of TempDB.So in case your database collation is different
then server collation,you should pay attention to COLLATION
issues when using temp tables.
8)Temp tables cannot be used in UDF(user defined function).
9)A table variable can use registered UDTs, user defined data
types, and xml collections in user current database,because
table variable inherits the user current database settings.
A temp table cannot use the types created in the user
database if the same types do not exist in the TempDB,
because it inherits the settings of TempDB.
I hope this will help you decide when and where to use
temp tables or table variables.
temp table or variable table. But using table variables for small
to medium volumes of data considered to be a rule of thumb.
One important thing - the table variable is NOT necessarily
memory resident. Under memory pressure, the pages
belonging to a table variable can be pushed out to tempdb.
Temp tables always stored in TempDB.
OK,lets see the main differences:
1) Table variables are out of scope of the transaction mechanism,
in other words the ROLLBACK is not going to work in case you
changed some data in variable table and rollback the changes.
Also table variables minimally logged and don't participate
in locking. This gives them some performance advantage due
to the fact that they don't require the overhead of locking
and logging.
2) A Stored Procedure containing a temp table will be
almost always recompiled. While s SP with table variable can
be statically compiled in advance.If you have a long SP that take
a while to compile you should consider whether to use the
temp tables.
3)Table variable can have Primary Key and Unique Constraints
defined. And the constraints can not be created in separate DDL
statements,only in table definition.table variable cannot have
regular indexes.The temp table can have all types of indexes.
4)Table variables are variables,this means that table variable
cannot be referenced by dynamic queries (you cannot 'see'
them in EXEC(string) statement) and in inner (sub) stored
procedures. One more limitation: table variables cannot be
used in an insert/exec (in SQL 2000) and 'Select into'
statements. Meaning you cannot insert SP`s result set into
table variable by using insert/exec method (in SQL 2000)
or use Select Into method:
insert into @table
exec sp_xxxxx
or
Select *
into @table
from SomeTable
*** The Insert/exec method can be used with table variables
in SQL 2005 and above.
5)Table variables don't have column statistics,temp tables
do have. In fact, the estimated row number in execution
plan for table variable is always 1. Without statistics,SQL Server
might choose a poor processing plan for a query that contains
a table variable.
6)Parallel query execution plans are not generated for queries
that modify table variables.They generated only for Select
statements that involve table variable(s).
7)In case of table variable the collations for the string
columns inherited from the collation of the current user
database,and in case of temp tables it will take the collation
of TempDB.So in case your database collation is different
then server collation,you should pay attention to COLLATION
issues when using temp tables.
8)Temp tables cannot be used in UDF(user defined function).
9)A table variable can use registered UDTs, user defined data
types, and xml collections in user current database,because
table variable inherits the user current database settings.
A temp table cannot use the types created in the user
database if the same types do not exist in the TempDB,
because it inherits the settings of TempDB.
I hope this will help you decide when and where to use
temp tables or table variables.