SQL
SQL Server Temp table and table variable
Temp Table:
Temp table is a table that exists temporarily in “tempdb” database to store intermediary results and actions can be performed on this data. A foreign key constraint cannot be created on temp table. The 2 temp tables are local temp table and global temp table.
Local temp tables are available to the session or connection that created tables. They are automatically deleted when the session is closed. “#” is used as prefix for table names to create them.
Global temporary tables are available to all connections and exists till all connections are closed. . “##” is used as prefix for table names to create them.
Table Variable:
The table variable stores data temporarily in memory in rows and columns. It can be used in functions
Difference between temp table and table variable
| Temp table | Table variable |
|---|---|
| Physically created in tempdb database | It is like a variable. Created in memory |
| Can create any index | Cannot create explicit index. Can have implicit indexes created by Primary key or Unique key |
| Support DDL like Alter, Create, Drop | Does not support DDL |
| Not allowed in user defined functions | Can be used in user defined functions |
| Can be dropped explicitly | Cannot be dropped explicitly. They are dropped when batch or Stored Procedure completes execution |
| Participate in transactions | Don’t participate in transactions |
Table-valued parameters:
They are used to send multiple rows of data to SQL statement. They are declared using table types