Memory-Optimized Table Variables

In addition to memory-optimized tables (for efficient data access) and natively compiled stored procedures (for efficient query processing and business logic execution) In-Memory OLTP introduces a third kind of object: the memory-optimized table type. A table variable created using a memory-optimized table type is a memory-optimized table variable.

Memory-optimized table variables offer the following advantages when compared to disk-based table variables:

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.

With memory-optimized table variables, there is no tempdb utilization. Table variables are not stored in tempdb and do not use any resources in tempdb.

The typical usage scenarios for memory-optimized table variables are:

Storing intermediate results and creating single result sets based on multiple queries in natively compiled stored procedures.

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.

Table variables can be used to simulate cursors in natively compiled stored procedures, which can help you work around surface area limitations in natively compiled stored procedures.

Like memory-optimized tables, SQL Server generates a DLL for each memory-optimized table type. (Compilation is invoked when the memory-optimized table type is created and not when used to create memory-optimized table variables.) This DLL includes the functions for accessing indexes and retrieving data from the table variables. 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. The table variable can then be used in the same way as disk-based table variables. You can insert, update, and delete rows in the table variable, and you can use the variables in Transact-SQL queries. You can also pass the variables into natively compiled and interpreted stored procedures, as table-valued parameters (TVP).

The data type and constraint restrictions on memory-optimized tables also apply to memory-optimized table types. For example, in SQL Server 2014 default constraints are supported, but check constraints are not.

Like memory-optimized tables, memory-optimized table variables,

Do not support parallel plans.

Must fit in memory and do not use disk resources.

Disk-based table variables exist in tempdb. Memory-optimized table variables exist in the user database (but they do not consume storage and are not recovered).

You cannot create a memory-optimized table variable using in-line syntax. Unlike disk-based table variables, you must create a type first.

The following sample script shows the declaration of a table variable as the memory-optimized table type Sales.SalesOrderDetailType_inmem, the insert of three rows into the variable, and passing the variable as a TVP into Sales.usp_InsertSalesOrder_inmem.

Memory-optimized table types can be used as the type for stored procedure table-valued parameters (TVPs) and can be referenced by clients exactly the same as disk-based table types and TVPs. Therefore, the invocation of stored procedures with memory-optimized TVPs, and natively compiled stored procedures works exactly the same as the invocation of interpreted stored procedures with disk-based TVPs.

The following sample shows how to store intermediate results and create single result sets based on multiple queries in natively compiled stored procedures. The sample is computing the union SELECT c1 FROM dbo.t1 UNION SELECT c1 FROM dbo.t2.

Memory consumption for table variables is similar to memory-optimized tables, with the exception of nonclustered indexes. If you insert a lot of rows into memory-optimized table variables with nonclustered indexes and if the index keys are large, these table variables will use a disproportionate amount of memory. Nonclustered indexes on large table variables require proportionately more memory than a nonclustered index would require for the same number of rows inserted into a table (more memory in the index pages).