A table variable used thousands of times can add pressure to tempdb. Memory-optimized table variables move that temporary structure into In-Memory OLTP, with setup and trade-offs worth testing.
Confirm the Actual Bottleneck
Memory-optimized table variables help when tempdb allocation or metadata contention from repeated temporary structures is a real bottleneck. They are not a universal replacement for every temporary table. Start with waits, execution plans, row counts, and workload frequency. A table variable holding a few rows in an infrequent procedure can already be fine.
I measure the path before proposing In-Memory OLTP setup. Adding a memory-optimized filegroup changes the database’s operational requirements. The change is justified when the current design produces a clear contention or latency problem. What wait or resource cost will this replacement reduce? If the answer is only that memory sounds faster, the investigation is unfinished.
Understand Where Memory-Optimized Table Variables Keep Rows
A memory-optimized table variable uses memory-optimized structures and does not use tempdb for its rows. Its type must include an index, and SQL Server needs a MEMORY_OPTIMIZED_DATA filegroup in the database. The variable is still scoped to its declaration, unlike a shared permanent table. Memory capacity and feature restrictions become part of the design.
I explain that no tempdb does not mean no cost. Memory is finite. Hash indexes need bucket planning, and nonclustered indexes have their own maintenance work. Test with realistic row volumes and concurrent sessions. A fast one-session example can hide the memory footprint of many workers using the variable at once.
Check the Database Prerequisite
Query filegroups in the target database before creating a memory-optimized type. On SQL Server, the database needs a memory-optimized data filegroup and a container. Adding one requires a reviewed file path, storage permissions, backup plan, and restore test. The path is a Windows directory under the server’s storage policy.
I do not add the filegroup during an incident just to try a type. Plan it as a database change. The query below shows whether the prerequisite exists. A user database can have only the supported memory-optimized filegroup arrangement, so reuse an existing one when present.
SELECT name, type_desc
FROM sys.filegroups
WHERE type_desc = N'MEMORY_OPTIMIZED_DATA_FILEGROUP';Create an Indexed Table Type
The type defines columns and at least one index. The sample uses a nonclustered index and requires the prerequisite filegroup. Create it once during deployment, not every time the procedure runs. Choose keys and column types based on the actual query pattern. A type is a schema object, so changes require deployment planning.
I start with a narrow schema. Copying every column from a disk table into a temporary type wastes memory. Keep only the values the procedure needs. The code demonstrates a type that can be used in the current database after setup.
CREATE TYPE dbo.OrderWorkType AS TABLE
(
OrderId bigint NOT NULL
INDEX IX_OrderWorkType_OrderId NONCLUSTERED,
StatusCode tinyint NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON);
Use Memory-Optimized Table Variables in a Procedure
Declare a variable of the type, insert rows, and join or filter as needed. The table variable is private to the execution context. It can also be passed as a table-valued parameter through supported client APIs. Test the application path, not only a hand-run query window.
I compare the same procedure using its previous table variable and the new type. Capture duration, CPU, tempdb waits, memory, and plan behavior under concurrency. The example below shows a small use of the type. It returns values from the variable and does not require an application table.
DECLARE @work dbo.OrderWorkType;
INSERT INTO @work (OrderId, StatusCode)
VALUES (1, 0), (2, 0);
SELECT OrderId, StatusCode
FROM @work
WHERE StatusCode = 0;Choose the Index Carefully
A hash index can work well for equality lookups when bucket count matches expected unique keys. A nonclustered index supports range and ordered access patterns more flexibly. The type must have an index, but more indexes consume memory and insertion work. Use the actual lookups and row volume to choose.
I avoid choosing a bucket count from a small test if production uses many concurrent rows. Overly low counts can create chains and slow lookups. Overly large counts waste memory. A nonclustered index is a reasonable simple start when the pattern is mixed. Measure the chosen design under representative inputs.
Watch Memory-Optimized Table Variables Under Concurrency
Every concurrent execution can hold its own variable. Multiply the peak rows per execution by overlapping sessions when planning memory. The engine’s memory-optimized object accounting and server memory pressure need monitoring. A replacement that reduces tempdb waits can still harm the system if it consumes too much memory.
I test during a realistic busy window. A single query getting faster is not enough if throughput falls. Check the wider application and failover behavior. Memory-optimized objects introduce operational dependencies that a conventional temp table does not. Make sure the recovery team knows the filegroup and type exist.
Know the Feature Limits
In-Memory OLTP supports a defined subset of data types and T-SQL behavior. Constraints, transactions, cross-database work, and query features need review for the current SQL Server version. A table variable replacement that changes semantics or fails in a rare branch is not a win. Read current support guidance for the actual build.
I move one proven hot path at a time. That makes a failure easier to diagnose and a rollback easier to perform. If a temporary table needs complex indexing or large intermediate results, it can remain the better tool. The goal is to remove a measured bottleneck, not to convert every @ variable to a new type.
Keep the Rollback Simple
Save the original procedure and test data shape before deployment. Plan how to switch back if memory pressure or a functional limit appears. The memory-optimized filegroup has its own lifecycle and should not be added casually. Verify backups and restores after the change so recovery remains complete.
Which metric improved after the replacement? If you cannot show reduced contention or better throughput under the same workload, reconsider the added complexity. Memory-optimized table variables are useful where tempdb is the bottleneck and the memory cost fits. They are not free speed hidden behind a new keyword.
Related reading on this blog: Table Variables or Temp Tables: Performance Comparison: SELECT and Generate In-Memory OLTP Migration Checklists: SSMS.
A memory-optimized table variable is not a faster spelling of @table, it is a different storage choice with a memory bill.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
