Memory-Optimized Table Variables vs Temp Tables in SQL Server

1. Introduction

In high-performance OLTP and reporting workloads, developers frequently use temporary data structures inside SQL Server procedures. The most common options are:

Memory-optimized table variables
Traditional table variables
Temp tables (#tables)
Memory-optimized table types (In-Memory OLTP)

With SQL Server offering multiple temporary storage mechanisms, the key question is:
When should you use a memory-optimized table variable instead of a temp table?

This article provides a complete, practical guide with examples, performance benchmarks, and real-world recommendations.

2. The Role of Temporary Data Structures

Temporary tables and table variables help store intermediate results inside:

Stored procedures
UDFs
ETL processes
Complex transactional logic
Data transformations

Choosing the wrong structure often leads to:

Excessive TempDB usage
Slow query performance
Incorrect execution plan estimates
Blocking or latch contention

Memory-optimized table variables solve many of these issues, but they are not always the right choice.

3. Temp Tables: How They Work

3.1 Temp table basics

CREATE TABLE #TempOrders (
    OrderId INT,
    Amount DECIMAL(10,2)
);

INSERT INTO #TempOrders VALUES (1, 500.00);

3.2 Characteristics

Stored in TempDB, not in memory
Can have indexes (clustered/nonclustered)
Support statistics, which helps the optimizer
Can be large; SQL Server spills to TempDB as required
Support transactions, rollback, constraints

3.3 Temp table advantages

Good for large datasets
Accurate cardinality estimates
Join-friendly
Suitable for multi-step transformations

3.4 Temp table disadvantages

TempDB I/O overhead
Latches and allocation contention
Slower performance for short-lived OLTP workloads
Compiles multiple plans due to table structure changes

4. Table Variables (Traditional) and Their Limitations

4.1 Example

DECLARE @Orders TABLE (
    OrderId INT,
    Amount DECIMAL(10,2)
);

Limitations

Stored in TempDB (despite the name)
No statistics
Poor cardinality estimates (always assumes 1 row)
No parallel plans
Cannot create nonclustered indexes inline
Performance degrades with more than a few thousand rows

Traditional table variables are only ideal for very small datasets (under a few hundred rows).

This is where memory-optimized table variables come into the picture.

5. Memory-Optimized Table Variables (In-Memory OLTP)

5.1 What they are

A memory-optimized table variable:

Uses in-memory storage
Has no TempDB usage
Uses latch-free, lock-free architecture
Needs a memory-optimized table type created first

5.2 Example

Step 1: Create a memory-optimized filegroup once:

ALTER DATABASE YourDB
ADD FILEGROUP InMemoryFG CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE YourDB
ADD FILE (NAME = 'InMemoryFile', FILENAME = 'C:\Data\InMemoryFile') 
TO FILEGROUP InMemoryFG;

Step 2: Create a memory-optimized table type:

CREATE TYPE dbo.OrderMemType AS TABLE
(
    OrderId INT NOT NULL,
    Amount DECIMAL(10,2),
    INDEX IX_OrderId HASH (OrderId) WITH (BUCKET_COUNT = 1024)
)
WITH (MEMORY_OPTIMIZED = ON);

Step 3: Declare the variable:

DECLARE @Orders dbo.OrderMemType;
INSERT INTO @Orders VALUES (1, 500.00);

6. Key Differences: Memory-Optimized Table Variables vs Temp Tables

6.1 Performance

Memory-optimized variables use in-memory data structures
Temp tables require TempDB writes, page allocations, latches
For small to medium datasets, memory-optimized variables are significantly faster

6.2 Locking and Latching

Temp tables use regular locking
Memory-optimized table variables are lock-free
Zero blocking even under high concurrency

6.3 Statistics

Temp tables: have statistics
Memory-optimized TVPs: do not have traditional statistics, but cardinality estimation is much more accurate than regular table variables

6.4 Logging

Temp tables: TempDB logging
Memory-optimized: Minimal logging

6.5 Parallelism

Temp tables: allow parallel plans
Memory-optimized: generally do not support parallelism inside them

6.6 Scalability

Temp tables can scale to millions of rows
Memory-optimized table variables are best for small to medium rows (up to 500k depending on memory overhead)

7. Benchmark Example: 100k Inserts

Test 1: Temp table

CREATE TABLE #Temp (Id INT, Val INT);
INSERT INTO #Temp SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 1 FROM sys.objects a CROSS JOIN sys.objects b;

Results:

CPU: high
Duration: slow
TempDB writes heavy

Test 2: Memory-optimized table variable

DECLARE @Mem dbo.MemTableType;
INSERT INTO @Mem SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 1 FROM sys.objects a CROSS JOIN sys.objects b;

Results:

CPU: lower
Duration: significantly faster
No TempDB usage

Performance improvement often ranges from 20% to 300%, depending on workload.

8. When to Use Memory-Optimized Table Variables

Use them when:

8.1 High concurrency OLTP workloads

Examples

Order creation
Inventory update
Financial transaction processing

They avoid lock/latch contention entirely.

8.2 Frequent small temporary data structures

For small datasets (1–50k rows):

Filtering
Aggregations
Parameter lists
Intermediate query results

8.3 TempDB bottlenecks

Memory-optimized variables remove TempDB pressure.

9. When NOT to Use Memory-Optimized Table Variables

Do not use them when:

9.1 You need parallel execution plans

Memory-optimized objects restrict parallelism.

9.2 You process large datasets

Memory-optimized tables must fit into memory.

9.3 You need complex indexing

Temp tables allow any index type.

9.4 You rely on statistics

Temp tables support statistics and are better for large joins.

10. Temp Tables vs Memory-Optimized Table Variables

Feature	Temp Table	Memory-Optimized Table Variable
Storage	TempDB	Memory
Concurrency	Blocking/latches	Non-blocking
Statistics	Yes	No (but better CE than table vars)
Indexing	Full	Hash and nonclustered only
Large Data Support	Excellent	Limited
Parallelism	Supported	Limited
Logging	TempDB logging	Minimal
Ideal For	Large transformations	High performance OLTP

11. Real-World Architecture Recommendation

Use Temp Tables When

Dataset > 100k rows
Complex joins or aggregations
Reporting, ETL, analytics

Use Memory-Optimized Table Variables When

OLTP workloads
Small-to-medium datasets
High concurrency systems
You want to eliminate TempDB waits

Hybrid Recommendation

Many enterprise systems follow this guideline:

Use memory-optimized variables inside transactional procs
Use temp tables inside reporting/ETL procs
Avoid traditional table variables almost entirely

12. Best Practices

12.1 Pre-create memory-optimized table types

Avoid on-the-fly creation.

12.2 Always define proper hash bucket count

General rule:

BucketCount = 2 × ExpectedRows

12.3 Monitor memory usage

Memory-optimized objects count towards database memory quota.

12.4 Keep row width small

Memory-optimized structures are RAM-heavy.

13. Conclusion

Memory-optimized table variables are a powerful addition to SQL Server’s temporary storage options. They offer:

Lock-free operations
High concurrency performance
Reduced TempDB pressure
Faster OLTP workloads

However, they are not a replacement for temp tables.
The correct choice depends on workload characteristics.

For most systems, the recommended pattern is:

Use temp tables for large datasets and complex logic
Use memory-optimized table variables for OLTP procedures handling small-medium sets
Avoid traditional table variables except for 10–50 rows