DECLARE @people TABLE
(
id INT,
name VARCHAR(32)
)

A table variable is created in memory, and so performs slightly better than #temp tables (also because there is even less locking and logging in a table variable). A table variable might still perform I/O to tempdb (which is where the performance issues of #temp tables make themselves apparent), though the documentation is not very explicit about this.

Table variables are automatically cleared when the procedure or function goes out of scope, so you don't have to remember to drop or clear the data (which can be a good thing or a bad thing; remember "release early"?). The tempdb transaction log is less impacted than with #temp tables; table variable log activity is truncated immediately, while #temp table log activity persists until the log hits a checkpoint, is manually truncated, or when the server restarts.

Table variables are the only way you can use DML statements (INSERT, UPDATE, DELETE) on temporary data within a user-defined function. You can create a table variable within a UDF, and modify the data using one of the above statements.

Table variables can lead to fewer stored procedure recompilations than temporary tables and — since they cannot be rolled back — do not bother with the transaction log.

Limitation of Table Variable :

1. Table variables are only allowed in SQL Server 2000+, with compatibility level set to 80 or higher.

2. You cannot use a table variable in either of the following situations:

INSERT @table EXEC sp_someProcedure

SELECT * INTO @table FROM someTable

3. You cannot truncate a table variable.

4. Table variables cannot be altered after they have been declared.

5. You cannot explicitly add an index to a table variable, however you can create a system index through a PRIMARY KEY CONSTRAINT, and you can add as many indexes via UNIQUE CONSTRAINTs.

6. Table variables are not visible to the calling procedure in the case of nested procs.

7. Table variables do not maintain statistics like temporary tables.

Do I have to use table variables instead of temporary tables?

The answer depends on these three factors:

1. The number of rows that are inserted to the table.
2. The number of recompilations the query is saved from.
3. The type of queries and their dependency on indexes and statistics for performance.

In some situations, breaking a stored procedure with temporary tables into smaller stored procedures so that recompilation takes place on smaller units is helpful.