Services
Technology Web App Development Mobile App Development Windows App Development Database Development UI/UX Development Cloud Development SEO Digital Marketing Guest Posting Content Writing Artificial Intelligence Data Science ERP Development CRM Development CMS Development LMS Development Business Listing Blockchain Technology Startup Promotion AI Tools Integration
Products
Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller
Developer
Article Blog Forum Interview Beginner News Startup Quiz
Users
Career
Apply Job Internship
Pricing
Login Sign Up Get a Quote

interview

Home Interviews Temp table vs Temp variable in SQL Server. – MindStick
Ask Question

1 Answers

Anubhav Sharma
Anubhav Sharma 27 Apr 2026
Report Abuse

In Microsoft SQL Server, both temporary tables and table variables are used to store temporary data, but they behave quite differently. Choosing the wrong one can hurt performance in real apps.

Quick Difference

Feature Temp Table (#Temp) Table Variable (@Table)
Storage TempDB TempDB
Scope Session Batch / Procedure
Indexing Full support Limited
Statistics Yes No (limited)
Performance Better for large data Better for small data
Transactions Supports rollback Limited support

1. Temp Table (#TempTable)

Create Example

CREATE TABLE #TempEmployees (
    Id INT,
    Name NVARCHAR(50)
);

Key Points

Stored in TempDB

Supports:

  • Indexes
  • Constraints
  • Statistics (important for query optimizer)
  • Can be used across multiple queries in same session

Best For:

  • Large datasets
  • Complex joins
  • Multiple operations

2. Table Variable (@TableVar)

Create Example

DECLARE @TempEmployees TABLE (
    Id INT,
    Name NVARCHAR(50)
);

Key Points

  • Also stored in TempDB (not memory only)
  • No full statistics → optimizer assumes small data
  • Scope limited to:
  • Current batch / stored procedure

Best For:

  • Small datasets
  • Simple logic
  • Lightweight operations

3. Performance Difference (Important)

Table Variable Problem

SQL Server assumes:

Estimated rows = 1

This can cause:

  • Bad execution plans
  • Slow joins on large data

4. Transaction Behavior

BEGIN TRAN;

INSERT INTO #TempEmployees VALUES (1, 'A');
ROLLBACK; -- Data removed

DECLARE @T TABLE (Id INT);
INSERT INTO @T VALUES (1);
ROLLBACK; -- Data may NOT rollback as expected

Temp tables behave better with transactions.

5. Indexing Support

Temp Table

CREATE INDEX IX_Id ON #TempEmployees(Id);

Table Variable

DECLARE @T TABLE (
    Id INT PRIMARY KEY
);

Only implicit indexes via constraints.

6. When to Use What

Use Temp Table if:

  • Data > 100 rows (rule of thumb)
  • Complex joins / aggregations
  • Performance matters

Use Table Variable if:

  • Data is very small
  • Inside stored procedure
  • Simple operations

Final Verdict

  • Temp Table = Powerful + Scalable
  • Table Variable = Lightweight + Limited
advertising
Markdown for AI

A clean, structured version of this page for AI assistants and LLMs.

Open .md