Prev Next

Temporary Tables

Temporary tables in SQL Server are used to store intermediate results temporarily. They are particularly useful when you need to break down complex queries or store data temporarily for use in a session. Temporary tables are created in the tempdb system database.

Types of Temporary Tables

  • Local Temporary Tables: Prefixed with a single # and only accessible in the session in which they were created.
  • Global Temporary Tables: Prefixed with double ## and accessible to all sessions until the last connection using the table is closed.

Creating a Local Temporary Table

Example: Creating and Using a Local Temporary Table

CREATE TABLE #TempFreedomFighters (
    FighterID INT,
    Name VARCHAR(50),
    City VARCHAR(50)
);

INSERT INTO #TempFreedomFighters (FighterID, Name, City)
VALUES (1, 'Mahatma Gandhi', 'Porbandar'),
       (2, 'Subhas Chandra Bose', 'Cuttack');

SELECT * FROM #TempFreedomFighters;

Output:

FighterIDNameCity
1Mahatma GandhiPorbandar
2Subhas Chandra BoseCuttack

Creating a Global Temporary Table

Example: Creating a Global Temporary Table

CREATE TABLE ##GlobalTempFreedomFighters (
    FighterID INT,
    Name VARCHAR(50),
    City VARCHAR(50)
);

INSERT INTO ##GlobalTempFreedomFighters (FighterID, Name, City)
VALUES (1, 'Bhagat Singh', 'Banga'),
       (2, 'Rani Lakshmi Bai', 'Varanasi');

Dropping Temporary Tables

Temporary tables are automatically dropped when the session ends (for local) or when all connections are closed (for global). However, you can explicitly drop them:

DROP TABLE #TempFreedomFighters;
DROP TABLE ##GlobalTempFreedomFighters;

Do's and Don'ts

Do's

  • Use temporary tables for storing intermediate results in complex queries.
  • Drop temporary tables explicitly if they are no longer needed to free resources.
  • Use appropriate indexing on temporary tables if performance is critical.

Don'ts

  • Don't use temporary tables excessively; consider table variables or CTEs for smaller datasets.
  • Don't forget that temporary tables are stored in tempdb, which can impact performance if overused.
  • Don't assume global temporary tables are secure; they are accessible across all sessions.
Prev Next