1. Introduction

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

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

3.3 Temp table advantages

3.4 Temp table disadvantages

4. Table Variables (Traditional) and Their Limitations

4.1 Example

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

Limitations

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

6.2 Locking and Latching

6.3 Statistics

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:

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

FeatureTemp TableMemory-Optimized Table Variable
StorageTempDBMemory
ConcurrencyBlocking/latchesNon-blocking
StatisticsYesNo (but better CE than table vars)
IndexingFullHash and nonclustered only
Large Data SupportExcellentLimited
ParallelismSupportedLimited
LoggingTempDB loggingMinimal
Ideal ForLarge transformationsHigh performance OLTP

11. Real-World Architecture Recommendation

Use Temp Tables When

Use Memory-Optimized Table Variables When

Hybrid Recommendation

Many enterprise systems follow this guideline:

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: