What is the difference between a temporary table and a table variable?

Question

Accepted Answer

Temporary tables are session-specific, while table variables are scoped to the batch or procedure they are defined in.

Temporary tables are created using 'CREATE TABLE #TempTable' and exist for the duration of the session.
Table variables are declared using '@TableVariable' and are limited to the scope of the batch or stored procedure.
Temporary tables can have indexes and statistics, while table variables cannot have indexes (except for primary keys).
Temporary tables can be used in complex queries and can be joined with other tables, while table variables are more limited in usage.
Example: 'SELECT * INTO #TempTable FROM SourceTable' creates a temporary table, while '@TableVariable TABLE (ID INT)' declares a table variable.

Interview Questions Asked to SQL Developer at Other Companies