← Back to Knowledge Base← Previous questionNext question →
ProgrammingBackend Developer

What are the features of Temporary Tables in SQL, how to use them properly, and what is the difference from Table Variables?

Pass interviews with Hintsage AI assistant

Answer.

Temporary Tables and Table Variables are different mechanisms for storing intermediate data temporarily during the execution of a query or procedure.

Temporary Tables (e.g., #TempTable in MS SQL):

  • Stored in the temporary database tempdb.
  • Visible in the current session or connection.
  • Allow the creation of indexes, constraints, and can be part of transactions.
  • Used when working with large volumes of data and complex processing.

Table Variables (e.g., @MyTable):

  • Have visibility within a batch/procedure/function.
  • Often optimized for small data sets.
  • Cannot have full indexes created on them (except PRIMARY KEY/UNIQUE).
  • Less likely to be blocked and participate in transactions differently.

Example:

-- Temporary table
CREATE TABLE #Temp(
  Id INT,
  Name NVARCHAR(100)
);
INSERT INTO #Temp VALUES (1, 'Test');
SELECT * FROM #Temp;

-- Table variable
DECLARE @MyTable TABLE (Id INT, Name NVARCHAR(100));
INSERT INTO @MyTable VALUES (2, 'Sample');
SELECT * FROM @MyTable;

Trick question.

What is the difference between the lifespan and visibility of the temporary table #TempTable and the global temporary table ##GlobalTempTable?

Correct answer:

  • #TempTable is only visible in the current session, destroyed when the connection is closed or explicitly dropped.
  • ##GlobalTempTable is available to all sessions until the last session that used the table is closed, or it is explicitly dropped. It disappears only after the last connection using it is closed.
-- Global temporary table
CREATE TABLE ##GlobalTemp (Id INT);

Examples of real mistakes due to lack of knowledge on the topic.

Story

In a project for processing large reports, a table variable was used for temporarily storing hundreds of thousands of rows. Result: a sharp performance drop, as the optimizer overestimated the cost of queries and began using poorly suited plans. Switching to a temporary table in tempdb resulted in a significant time gain.

Story

In one procedure, temporary table deletion was forgotten. During repeated batch executions in one session, errors occurred: "object already exists". The issue was resolved after adding DROP TABLE before creation.

Story

Global temporary table was used for inter-process communication of reports. It was not considered that simultaneous execution made the behavior unpredictable — sessions conflicted over the same data. A decision was made to replace the global table with individual temporary tables for each session.