SQL Server 2014 In-Memory OLTP introduces two new widely advertised kinds of database objects: memory-optimized tables for efficient data access, and natively compiled stored procedures for efficient query processing and business logic execution. However, there is a third kind of object introduced by In-Memory OLTP: the memory-optimized table type.
The use of memory-optimized table variables has a number of advantages over traditional table variables:
- The variables are truly in memory: they are guaranteed to never spill to disk.
- Data access is more efficient due to the same memory-optimized algorithm and data structures used for memory-optimized tables, especially when the variables are used in natively compiled stored procedures.
- Finally, with memory-optimized table variables there is no tempdb utilization: table variables are not 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.
- Passing table-valued parameters into natively compiled stored procedures, as well as traditional interpreted procedures.
- As replacement for traditional table variables, and in some cases for #temp tables that are local to a stored procedure. This is particularly useful if there is a lot of tempdb contention in the system.
Like memory-optimized tables, SQL Server generates a DLL for each memory-optimized table type. As is the case for memory-optimized tables, the DLL includes the functions for access 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 traditional table variables: you can insert/update/delete rows in the table variable, and you can use the variables in T-SQL queries. You can also pass them into natively compiled as well as traditional stored procedures, as table-valued parameters (TVP).
The following sample shows a memory-optimized table type from the AdventureWorks-based In-Memory OLTP sample.
CREATE TYPE [Sales].[SalesOrderDetailType_inmem] AS TABLE(
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[LocalID] [int] NOT NULL,
INDEX [IX_ProductID] HASH ([ProductID])
WITH ( BUCKET_COUNT = 8),
INDEX [IX_SpecialOfferID] HASH ([SpecialOfferID])
WITH ( BUCKET_COUNT = 8)
WITH ( MEMORY_OPTIMIZED = ON )
As you can see, the syntax is very similar to traditional table variables. A few things to keep in mind:
- The option MEMORY_OPTIMIZED=ON indicates that the table type is indeed memory-optimized.
- The type must have at least one index. Like with tables, supported index types are hash and nonclustered.
- Keep in mind that for a hash index the bucket_count should be about 1-2X the number of expected unique index keys. Over-sizing is usually better than under-sizing: if sometimes you insert only 2 values in the variables, but sometimes insert up to 1000 values, it’s usually better to specify BUCKET_COUNT=1000.
- The restrictions on memory-optimized tables related to data types and constraints 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, table variables do not support parallel plans.
- Like memory-optimized tables, table variables must fit completely in memory – there is no spill to disk.
- Traditional table variables exist in tempdb. In contrast, memory-optimized table variables exist in the user database.
- Memory-optimized table types need to be specified explicitly using a CREATE TYPE statement. Specification in-line with the variable declaration is not supported.
The following example script shows declaration of a table variable as the memory-optimized table type Sales.SalesOrderDetailType_inmem, insert of three rows into the variable, and passing the variable as a TVP into Sales.usp_InsertSalesOrder_inmem.
DECLARE @od Sales.SalesOrderDetailType_inmem,
@DueDate datetime2 = SYSDATETIME()
INSERT @od (LocalID, ProductID, OrderQty, SpecialOfferID) VALUES
@SalesOrderID = @SalesOrderID,
@DueDate = @DueDate,
@OnlineOrderFlag = 1,
@SalesOrderDetails = @od
Note that memory-optimized table types can be used as the type for stored procedure table-valued parameters (TVPs), and can be referenced by clients in exactly the same way as traditional table types and TVPs. Therefore, invocation of stored procedures with memory-optimized TVPs, and indeed natively compiled stored procedure, works exactly the same way as the invocation of traditional stored procedures with traditional TVPs.
In SQL Server 2014, memory-optimized tables do not support cross-database transactions. This means you cannot access another database from the same transaction or the same query that also accesses a memory-optimized table. This means you cannot straightforwardly copy data from a table in one database, to a memory-optimized table in another database.
Table variables are not transactional. Therefore, memory-optimized table variables can be used in cross-database queries, and can thus facilitate moving data from one database into memory-optimized tables in another. The idea is to use two transactions: in the first transaction you insert the data from the remote table into the variable; in the second transaction you insert the data into the local memory-optimized table from the variable.
For example, if you want to copy the row from table t1 in database db1 to table t2 in db2, using variable @v1 of type dbo.tt1, you would use something like:
DECLARE @v1 dbo.tt1
INSERT @v1 SELECT * FROM db1.dbo.t1
INSERT dbo.t2 SELECT * FROM @v1