- The scope of Local temporary tables is in the current session and Global temporary tables is till the connection exists.
- Can Truncate Temporary tables
- Can be part of a Transaction
- We pre-fix the table name with #(Local) or ##(Global)
- Can be altered
- Can create Non Clustered Indexes
- Can use WITH (NOLOCK) after the table name
- Can insert data in many ways as physical tables. (Ex: Insert into values, Insert Select, Select Into….)
- Can create Local and Global Temporary tables as well
- Ex: 1. CREATE TABLE #TableA (Columns to be Created) — Local Temporary table.
2. CREATE TABLE ##TableB (Columns to be Created) — Global Temporary table.
- The scope of the table variable only inside the batch
- Can’t Truncate Table variables
- Can’t be altered
- We pre-fix the table name with @
- Can’t be part of a Transaction
- Can’t create Non Clustered Indexes
- Can’t use WITH (NOLOCK) as there is no Lock concept in table variables
- Can’t use SELECT INTO
- Can’t create Global Table variables
- Ex: DECLARE @TableC TABLE (Columns to be created)
My suggestion is go for Table variables when we know that the structure of the table is not going to change in future. Otherwise go for Temporary tables.