SQL  

Variable Table vs Temporary Table in SQL Server — Explained with Examples

When working with SQL Server, developers often need to store intermediate results temporarily. Two popular approaches are Table Variables (@TableVariable) and Temporary Tables (#TempTable).
Although both can be used to store temporary data, they behave differently in terms of scope, performance, and usage.

In this article, we’ll explore:

  • ✅ What are Table Variables & Temporary Tables

  • ⚙️ Key Differences

  • 💻 Practical Examples

  • 🚀 Performance Comparison

  • 🔗 Useful References

📋 1. What is a Table Variable?

A Table Variable is a variable that stores data in a tabular form within memory. It is declared using the @ symbol.

🔧 Syntax

DECLARE @Students TABLE
(
    ID INT IDENTITY(1,1),
    Name NVARCHAR(100),
    Marks INT
);

INSERT INTO @Students (Name, Marks)
VALUES ('Amit', 85), ('Priya', 90), ('Rohan', 78);

SELECT * FROM @Students;

📌 Characteristics

  • Scope is limited to the batch, function, or stored procedure where it is declared.

  • Usually stored in memory, but large datasets may spill to tempdb.

  • No support for indexes except primary key and unique constraints.

  • No statistics, which may affect query optimization.

🧾 2. What is a Temporary Table?

A Temporary Table behaves like a regular table but exists only for the session or connection in which it was created.

🔧 Syntax

CREATE TABLE #Employees
(
    ID INT IDENTITY(1,1),
    Name NVARCHAR(100),
    Department NVARCHAR(50)
);

INSERT INTO #Employees (Name, Department)
VALUES ('Vipin', 'IT'), ('Ritika', 'HR'), ('Karan', 'Finance');

SELECT * FROM #Employees;

📌 Characteristics

  • Stored in the tempdb database.

  • Can have indexes, constraints, and statistics.

  • Can be modified multiple times within the same session.

  • Automatically dropped when the session ends.

⚖️ 3. Key Differences Between Table Variables and Temporary Tables

FeatureTable Variable (@Table)Temporary Table (#Table)
ScopeLimited to batch or procedureExists for the entire session
Storage LocationIn memory (tempdb if large)Always stored in tempdb
Transaction LoggingMinimalFully logged in tempdb
IndexesOnly primary/unique keysFull indexing support
StatisticsNot maintainedMaintained for better performance
Performance (Small Data)FasterSlightly slower
Performance (Large Data)SlowerFaster due to indexing
RecompilationDoesn’t cause recompilationMay cause stored proc recompilation
Usage in Stored ProceduresBest for small setsBest for large or complex joins

💡 4. Performance Comparison Example

Let’s test both in action.

Example. Comparing Speed

-- Temporary TableCREATE TABLE #TempTest (ID INT, Value NVARCHAR(100));
INSERT INTO #TempTest
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 'TempTable'FROM sys.objects a CROSS JOIN sys.objects b;

-- Table VariableDECLARE @VarTest TABLE (ID INT, Value NVARCHAR(100));
INSERT INTO @VarTestSELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 'VarTable'FROM sys.objects a CROSS JOIN sys.objects b;

If you check the execution time in SQL Server Management Studio (SSMS):

  • For small datasets, both perform similarly.

  • For large datasets, #TempTable often performs better due to statistics and indexing.

🧩 5. When to Use Which?

ScenarioRecommended Option
Small, in-memory data operations✅ Table Variable
Large data operations with joins✅ Temporary Table
Inside stored procedures (light data)✅ Table Variable
Need indexing or statistics✅ Temporary Table
Multi-step transformations✅ Temporary Table

🧠 6. Real-World Use Case

In a stored procedure, you might use a Table Variable to hold filtered results temporarily:

CREATE PROCEDURE usp_GetTopStudents
ASBEGIN
    DECLARE @TopStudents TABLE (Name NVARCHAR(100), Marks INT);

    INSERT INTO @TopStudents
    SELECT Name, Marks FROM Students WHERE Marks > 80;

    SELECT * FROM @TopStudents;
END

For large reports, you might prefer a Temporary Table:

CREATE PROCEDURE usp_GenerateReport
ASBEGIN
    CREATE TABLE #ReportData (ID INT, Sales DECIMAL(10,2), Region NVARCHAR(50));

    INSERT INTO #ReportData
    SELECT ID, Sales, Region FROM SalesData WHERE Year = 2025;

    SELECT Region, SUM(Sales) AS TotalSales FROM #ReportData GROUP BY Region;
END

📚 7. External References

For deeper study, check these official and community resources:

  • 🔗 Microsoft Docs: Table Variables (Transact-SQL)

  • 🔗 Microsoft Docs: Temporary Tables in SQL Server

  • 🔗 SQLShack: SQL Server Table Variable vs Temporary Table

  • 🔗 Redgate Blog: Table Variables and Temp Tables – Performance Explained

🏁 Conclusion

Both Table Variables and Temporary Tables are useful tools for managing temporary data in SQL Server.
Use Table Variables for small, lightweight data operations inside stored procedures, and use Temporary Tables when working with large datasets, indexing, or complex joins.

Membership not found