Official News from Microsoft’s Information Platform
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 typical usage scenarios for memory-optimized table variables are:
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 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