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:
Choosing the wrong structure often leads to:
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
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:
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
6.5 Parallelism
6.6 Scalability
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:
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
They avoid lock/latch contention entirely.
8.2 Frequent small temporary data structures
For small datasets (1–50k rows):
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
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:
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