Memory Optimized Table Variable in SQL Server

This amazing feature (In-Memory Table Variables) was introduced in SQL Server 2014 to provide the better performance for storing temporary data within memory and process with in the stored Procedure or other T-SQL Scripts.

A table variable created using a memory-optimized table type is a memory-optimized table variable because SQL Server does not give us the permission to create an In-memory table variable directly with declare clause.

Memory-optimized table variables advantages or limitations:

• The variables are only stored in memory. Data access is more efficient because memory-optimized table type use the same memory-optimized algorithm and data structures used for memory-optimized tables, especially when the variables are used in natively compiled stored procedures.
• Another big advantages with memory-optimized table variables are that there is no tempdb utilization. Means Table variables are not stored in tempdb and do not use any resources in tempdb. 
• Like memory-optimized tables, memory-optimized table variables do not support parallel plans and must fit in memory and do not use disk resources.

To understand this, we are going to take a dummy customer table having CustNo and CustName columns as given below:  

CustNo	CustName
20541	Lenovo Ltd.
20411	Amity University
20021	CMC Computer Ltd
20281	MRI Inc.
20151	NIIT Technologies

Based on the above table, we have to create a Memory Optimized Table Type called Customer_InMemory which is going to be used by Memory Optimized Table Variable as given below-

----- Create Memory Optimized Table Type CREATE TYPE dbo.Customer_InMemory AS TABLE(      CustNo INT NOT NULL     ,CustName NVARCHAR(50) NOT NULL     INDEX [Ind_CustNo] HASH (CustNo)                 WITH ( BUCKET_COUNT = 2000) ) WITH ( MEMORY_OPTIMIZED = ON ) GO

We have created Memory Optimized Table Type which is going to use by Memory Optimized Table Variable as given below-

---- Declare In-Memory Table Variable Declare @Customers as Customer_InMemory ---- Insert values into In-Memory Table Variable INSERT INTO @Customers (CustNo, CustName) SELECT CustNo, CustName From [dbo].[CustMaster] ---- Pull values from In-Memory Table Variable SELECT CustNo, CustName From @Customers ---- Output CustNo CustName 20541 Lenovo Ltd. 20411 Amity University 20021 CMC Computer Ltd 20281 MRI Inc. 20151 NIIT Technologies

Note: When a memory-optimized table variable is declared based on the table type, an instance of the table and index structures corresponding to the table type is created in the user session. 

With the help of the Memory Optimized Table Variables, we can increase the T-SQL queries performance. There are many typical usage scenarios where memory-optimized table variables could be more usable such as:

1. If you want to store intermediate results then you can create a single result sets based on multiple queries in natively compiled stored procedures.
2. Passing table-valued parameters into natively compiled stored procedures and interpreted stored procedures.
3. Replacing disk-based table variables, and in some cases #temp tables that are local to a stored procedure. This is particularly useful if there is a lot of tempdb contention in the system.
4. Table variables can be used to simulate cursors in natively compiled stored procedures, which can help us work around surface area limitations in natively compiled stored procedures.

Conclusion

In-memory table variable can be used in the same way as disk-based table variables. We can insert, update, and delete rows in the table variable, and use them in Transact-SQL queries. We can also pass the variables into natively compiled and interpreted stored procedures, as table-valued parameters. They do not interact with TempDb. So, Memory-optimization results in speed increases that are often 10 times faster or more.

References -Microsoft MSDN