Table Variables

The syntax for creating table variables is quite similar to creating either regular or temporary tables.  The only differences involve a naming convention unique to variables in general, and the need to declare the table variable as you would any other local variable in Transact SQL:

As you can see the syntax bridges local variable declaration (DECLARE @variable_name variable_data_type) and table creation (column_name, data_type, nullability).  As with any other local variable in T-SQL, the table variable must be prefixed with an "@" sign.  Unlike temporary or regular table objects, table variables have certain clear limitations.

    * Table variables can not have Non-Clustered Indexes

    * Statistics can not be created against table variables

Similarities with temporary tables include:

    * Instantiated in tempdb

    * Just as with temp and regular tables, users can perform all Data Modification Language (DML) queries against a table variable:  SELECT, INSERT, UPDATE, and DELETE. 

An interesting limitation of table variables comes into play when executing code that involves a table variable.  A table variable's lifespan is only for the duration of the transaction that it runs in.  

     

An unofficial rule-of-thumb for usage is to use table variables for returning results from user-defined functions that return table values and to use temporary tables for storage and manipulation of temporary data; particularly when dealing with large amounts of data.  However, when lesser row counts are involved, and when indexing is not a factor, both table variables and temporary tables perform comparably.  It then comes down to preference of the individual responsible for the coding process.

Table variable use fewer resource than a temporary table because of there limited scope. Transactions touching table variables only last for the duration of the update on the table variable, so there is less locking and logging overhead. This is also gives better performance to the table variable as against the temporary table.

We can also declare constraint, primary key, identity columns, and default value in the table variable very easily. SQL server does not maintain any statistics on the table variable [Remember statistics are heavily used by the query optimizer to determine the best method to execute]. 

Also we cannot change the definition of the table after the table is created. This means that we cannot use the alter table statement on a table variable. If we are using a table variable in a join, you will need to alias the table in order to execute the query.

Temporary Tables

      Temporary tables are created in tempdb.  The name "temporary" is slightly misleading, for even though the tables are instantiated in tempdb, they are backed by physical disk and are even logged into the transaction log.  They act like regular tables in that you can query their data via SELECT queries and modify their data via UPDATE, INSERT, and DELETE statements.  If created inside a stored procedure they are destroyed upon completion of the stored procedure.  Furthermore, the scope of any particular temporary table is the session in which it is created; meaning it is only visible to the current user.  Multiple users could create a temp table named #TableX and any queries run simultaneously would not affect one another - they would remain autonomous transactions and the tables would remain autonomous objects.  You may notice that my sample temporary table name started with a "#" sign.  This is the identifier for SQL Server that it is dealing with a temporary table.

Temporary tables act like physical tables in many ways.  You can create indexes and statistics on temporary tables.  You can also apply Data Definition Language (DDL) statements against temporary tables to add constraints, defaults, and referential integrity such as primary and foreign keys.  You can also add and drop columns from temporary tables.

                                                 Temporary tables are usually preferred over table variables for a few important reasons: they behave more like physical tables in respect to indexing and statistics creation and lifespan. 

A local temporary table, #table_name, exists only for the duration of a user session or the procedure that created the temporary table. When the user logs off or when the procedure that created the table completes, the local temporary table is lost. Multiple users can't share a local temporary table because it is local to one user session. 

A global temporary table, ##table_name, also exists for the duration of a user session or the procedure that created the table. When the last user session that references the table disconnects, the global temporary table is lost. However, multiple users can access a global temporary table; in fact, all other database users can access it. 


Any procedure with a temporary table cannot be pre-compiled