Table variables Vs Temporary table

Table variables: It’s similar to local variable, they are used to hold small amount of data (around 500 rows) and available within the scope of the batch, stored procedure,  functions.

Syntax for table variables:

  • The advantage of table variables over CTE is that they accessed during the entire batch or session.

Temporary tables: are used to hold the intermediate data operations, and all operations are quite similar to normal table but these are limited to scope.

there two types of temporary table based on the scope.

Local temporary tables: Local temporary tables are available within the scope of current session and dropped at the end of session. They must be prefix by “#” sign.

Global temporary tables: global temporary tables are available for all the sessions and dropped  when the session that created them and all the referencing session are closed.  They must be prefix by “##” sign.

The syntax for operations (create, droped, delete..) of these table are exactly same as traditional table.

  • It will use tempDB database space (system database).

  • Indexes should not be added to temporary tables after they created because doing so could affect negative to execution plan