SQL Server Table Variable — Unique Points

In Microsoft SQL Server a table variable (DECLARE @table TABLE) has several characteristics that are not common in many other RDBMS temporary table implementations.

Key Distinctive Features

1. Declared as a variable

A table is declared using DECLARE like a scalar variable.

Many databases require CREATE TEMP TABLE.

2. Usable inside functions

Table variables can be used inside user-defined functions, including:

• inline table-valued functions

• multi-statement table-valued functions

Many RDBMS do not allow temporary tables inside functions.

3. Not affected by transaction rollback

Data stored in a table variable remains after ROLLBACK, which makes it useful for debugging or logging intermediate steps.

4. Automatic scope lifetime

The table variable automatically disappears when the batch, procedure, or function ends, without requiring DROP.

5. Direct participation in joins

Table variables behave as full relational sources and can directly participate in joins with regular tables.

Very short summary

A SQL Server table variable is a scoped relational variable that:

• is declared with DECLARE

• can be used inside functions

• survives transaction rollback

• automatically disappears at scope end

• can participate directly in joins