A Memory-Optimized Table Variable: What Is It?
A unique kind of table variable that makes use of SQL Server's In-Memory OLTP engine is called a Memory-Optimized Table Variable. It is maintained in memory, which lowers tempdb contention and improves performance for workloads involving frequent data processing, in contrast to ordinary table variables or temporary tables (#temp).

Why is it Useful?
Memory-Optimized Table Variables provide.

• Faster performance: Avoids disk I/O by keeping data in memory.
• Tempdb contention reduction: Traditional temp tables and table variables rely on tempdb, which can be a bottleneck.
• Optimized latch-free concurrency: Uses memory-optimized data structures for ultra-fast access.
• Efficient for short-lived data: Ideal for scenarios where data exists only within a session or batch.

When to Use It?
You should use Memory-Optimized Table Variables when,

• Your queries experience tempdb contention.
• You perform frequent batch operations with temporary data.
• You need high-performance OLTP workloads with frequent inserts and lookups.
• You are working with stored procedures that rely on table variables.

Where to Use It?

• Stored procedures that process intermediate data.
• ETL workloads require temporary staging tables.
• High-performance transaction processing systems.
• Session-based data caching to avoid repeated database calls.

How to Use a Memory-Optimized Table Variable?
1. Enable Memory-Optimized Tables in the Database
Before using memory-optimized table variables, you must enable In-Memory OLTP.

ALTER DATABASE YourDatabase
ADD FILEGROUP MemoryOptimizedFG CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE YourDatabase
ADD FILE (NAME = 'MemOptData', FILENAME = 'C:\Data\MemOptData')
TO FILEGROUP MemoryOptimizedFG;

2. Declare a Memory-Optimized Table Variable
Unlike a regular table variable, you must use MEMORY_OPTIMIZED = ON.
DECLARE @MemOptTable TABLE
(
ID INT NOT NULL PRIMARY KEY NONCLUSTERED,
Name NVARCHAR(100) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON);

3. Insert and Query Data Efficiently
INSERT INTO @MemOptTable (ID, Name) VALUES (1, 'SQL Server'), (2, 'DBA Expert');

SELECT * FROM @MemOptTable;

4. Compare with Traditional Table Variables
A traditional table variable.
DECLARE @TableVar TABLE (ID INT, Name NVARCHAR(100));

Uses tempdb, causing I/O overhead.
Suffers from locking and latching under high concurrency.

Memory-Optimized Table Variables

• Avoid tempdb entirely.
• Are lock-free and latch-free, making them 10x faster in some cases.

Real-Time Example: Improving Performance in a High-Traffic System

• Scenario: A banking application processes thousands of real-time transactions per second. Using traditional table variables slows down the system due to tempdb contention.
• Solution: By replacing standard table variables with Memory-Optimized Table Variables, the system eliminates tempdb bottlenecks, resulting in a 40% faster transaction processing time.

DECLARE @TransactionLog TABLE
(
TransactionID INT NOT NULL PRIMARY KEY NONCLUSTERED,
AccountID INT NOT NULL,
Amount DECIMAL(10,2) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON);

Memory-optimized table Variables were introduced in SQL Server 2016 and are available in the following versions.
Compatible SQL Server Versions
SQL Server 2016 (13.x)
SQL Server 2017 (14.x)
SQL Server 2019 (15.x)
SQL Server 2022 (16.x)

Not Available In
SQL Server 2014 and earlier (Memory-optimized tables were introduced in 2014, but table variables were not supported as memory-optimized until 2016).

Best Practices & Recommendations
Enable In-Memory OLTP before using Memory-Optimized Table Variables.
Use them in stored procedures or batch processing scenarios for best performance.
Avoid using them for large datasets due to memory limitations.
Use Non-Clustered Indexes properly to avoid performance bottlenecks.
Test with different workloads before applying in production environments.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Using ROW_NUMBER() and PARTITION BY (Preferred Approach)
The ROW_NUMBER() function assigns a unique row number to each record within a partition (group). We can use this to identify and delete duplicates while keeping only the required data.

Query Syntax
WITH CTE AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY id) AS row_num
    FROM table_name
    WHERE condition  -- Apply filtering condition here
)
DELETE FROM CTE
WHERE row_num > 1;

Example
Consider a Customer table with duplicate entries based on Email.

Removing Duplicates While Keeping the First Entry.

;WITH CTE AS (
    SELECT ID
    FROM (
        SELECT ID,
               ROW_NUMBER() OVER (PARTITION BY NAME, Email, City ORDER BY ID) AS RN
        FROM Customers
        WHERE City = 'NY'  -- Only NY state filtering condition
    ) AS sub
    WHERE RN > 1
)
DELETE FROM Customers
WHERE ID IN (SELECT ID FROM CTE);

Explanation of the Query
Identifies Duplicates

• ROW_NUMBER() OVER (PARTITION BY Name, Email, City ORDER BY ID)
• Assign a row number (RN) for each duplicate group, keeping the first record (RN = 1).

Filters Out Duplicates (RN > 1): Only marks duplicate records where City = 'NY'.
Deletes Duplicate Records: Deletes only IDs from the CTE that have RN > 1
This syntax will be useful when we are joining more tables and deleting duplicates from one specific table.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.