Memory-Optimized 表格变量

2017-07-14

除了内存优化表（用于高效数据访问）和本机编译的存储过程（用于高效查询处理和业务逻辑执行），In-Memory OLTP 还引入了第三种类型的对象：内存优化表类型。使用内存优化表类型创建的表变量是内存优化表变量。

与基于磁盘的表变量相比，内存优化表变量具有以下优势：

变量仅存储在内存中。数据访问更高效，因为内存优化表类型使用与内存优化表相同的内存优化算法和数据结构，特别是在原生编译的存储过程中使用变量时。
使用内存优化的表变量时，不会利用 tempdb。表变量不存储在 tempdb 中，并且不使用 tempdb 中的任何资源。

内存优化表变量的典型使用方案包括：

储存中间结果，并利用本机编译存储过程中的多个查询来创建单个结果集。
将表值参数传递到本机编译的存储过程和解释的存储过程。
替换基于磁盘的表变量，以及在某些情况下替换存储过程局部的临时表。如果系统中存在大量 tempdb 资源冲突，这尤其有用。
表变量可以用于在本机编译的存储过程中模拟游标，这有助于解决本机编译的存储过程中的表面区域限制。

与内存优化表一样，SQL Server 为每个内存优化表类型生成 DLL。（在创建内存优化表类型时而不是用于创建内存优化表变量时调用编译。此 DLL 包括用于访问索引和从表变量检索数据的函数。当基于表类型声明内存优化表变量时，会在用户会话中创建与表类型对应的表和索引结构的实例。然后，可以使用与基于磁盘的表变量相同的方式使用表变量。可以在表变量中插入、更新和删除行，并且可以在 Transact-SQL 查询中使用变量。可以将变量传入本机编译和解释的存储过程，作为表值参数（TVP）。

以下示例显示了基于 AdventureWorks 的 In-Memory OLTP 示例（SQL Server 2014 In-Memory OLTP 示例）中的内存优化表类型。

CREATE TYPE Sales.SalesOrderDetailType_inmem
   AS TABLE
(
   OrderQty         smallint   NOT NULL,
   ProductID        int        NOT NULL,

   SpecialOfferID   int        NOT NULL
      INDEX  IX_SpecialOfferID  NONCLUSTERED,

   LocalID          int        NOT NULL,

   INDEX IX_ProductID HASH (ProductID)
      WITH ( BUCKET_COUNT = 8 )
)
WITH ( MEMORY_OPTIMIZED = ON );

此示例显示内存优化表类型的语法类似于基于磁盘的表类型，但有以下例外：

MEMORY_OPTIMIZED=ON 指示表类型是内存优化的。
该类型必须至少有一个索引。与内存优化表一样，可以使用哈希和非聚集索引。

对于哈希索引，存储桶计数应大约是预期唯一索引键数的一到两倍。有关详细信息，请参阅确定哈希索引的正确桶数。
内存优化表的数据类型和约束限制也适用于内存优化表类型。例如，SQL Server 2014 默认约束受支持，但检查约束不受支持。

与内存优化表、内存优化表变量一样，

不支持并行计划。
必须能在内存中运行，而不使用磁盘资源。

基于磁盘的表变量存在于 tempdb 中。内存优化表变量存在于用户数据库中（但它们不使用存储且未恢复）。

不能使用内联语法创建内存优化表变量。与基于磁盘的表变量不同，必须先创建类型。

表值参数

以下示例脚本将表变量声明为内存优化表类型 Sales.SalesOrderDetailType_inmem，将三行插入变量，并将变量作为 TVP 传入 Sales.usp_InsertSalesOrder_inmem。

DECLARE @od Sales.SalesOrderDetailType_inmem,  
  @SalesOrderID uniqueidentifier,  
  @DueDate datetime2 = SYSDATETIME()  
  
INSERT @od (LocalID, ProductID, OrderQty, SpecialOfferID) VALUES  
  (1, 888, 2, 1),  
  (2, 450, 13, 1),  
  (3, 841, 1, 1)  
  
EXEC Sales.usp_InsertSalesOrder_inmem  
  @SalesOrderID = @SalesOrderID,  
  @DueDate = @DueDate,  
 @OnlineOrderFlag = 1,  
  @SalesOrderDetails = @od

内存优化表类型可用作存储过程表值参数（TVP）的类型，客户端可以像使用基于磁盘的表类型和 TVP 一样引用它们。因此，调用带有内存优化 TVP 的存储过程，以及本机编译的存储过程，与调用带有基于磁盘的 TVP 的解释存储过程的方式完全相同。

#temp 表替换

以下示例显示内存优化表类型和表变量，作为存储过程本地 #temp 表的替代项。

-- Using SQL procedure and temp table  
CREATE TABLE #tempTable (c INT NOT NULL PRIMARY KEY NONCLUSTERED)  
  
CREATE PROCEDURE sqlProc  
AS  
BEGIN  
  TRUNCATE TABLE #tempTable  
  
  INSERT #tempTable VALUES (1)  
  INSERT #tempTable VALUES (2)  
  INSERT #tempTable VALUES (3)  
  SELECT * FROM #tempTable  
END  
GO  
  
-- Using natively compiled stored procedure and table variable  
CREATE TYPE TT AS TABLE (c INT NOT NULL PRIMARY KEY NONCLUSTERED)  
GO  
  
CREATE PROCEDURE NCSPProc  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS  
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')  
  DECLARE @tableVariable TT  
  INSERT @tableVariable VALUES (1)  
  INSERT @tableVariable VALUES (2)  
  INSERT @tableVariable VALUES (3)  
  SELECT c FROM @tableVariable  
END  
GO

创建单个结果集

下面的示例演示如何在本机编译的存储过程中存储中间结果，并基于多个查询创建单个结果集。该示例正在计算集合的联合 SELECT c1 FROM dbo.t1 UNION SELECT c1 FROM dbo.t2。

CREATE DATABASE hk  
GO  
ALTER DATABASE hk ADD FILEGROUP hk_mod CONTAINS MEMORY_OPTIMIZED_DATA  
ALTER DATABASE hk ADD FILE( NAME = 'hk_mod' , FILENAME = 'c:\data\hk_mod') TO FILEGROUP hk_mod;  
  
USE hk  
GO  
  
CREATE TYPE tab1 AS TABLE (c1 INT NOT NULL, INDEX idx NONCLUSTERED(c1)) WITH (MEMORY_OPTIMIZED = ON)  
  
CREATE TABLE dbo.t1 (c1 INT NOT NULL, INDEX idx NONCLUSTERED(c1)) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)  
CREATE TABLE dbo.t2 (c1 INT NOT NULL, INDEX idx NONCLUSTERED(c1)) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)  
  
INSERT INTO dbo.t1 VALUES (1), (2)  
INSERT INTO dbo.t2 VALUES (3), (4)  
GO  
  
CREATE PROCEDURE dbo.p1  
  WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
  AS  
  BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english' )  
  
    DECLARE @t dbo.tab1  
    INSERT @t (c1)  
    SELECT c1 FROM dbo.t1;  
  
    INSERT @t (c1)  
    SELECT c1 FROM dbo.t2;  
  
    SELECT c1 FROM @t;  
  END  
GO  
  
EXEC dbo.p1  
GO

表变量的内存消耗

表变量的内存消耗类似于内存优化表，但非聚集索引除外。如果将大量行插入具有非聚集索引的内存优化表变量中，并且索引键较大，则这些表变量将使用不成比例的内存量。大型表变量上的非聚集索引需要的内存比例明显高于插入到表中的相同行数时所需的内存（尤其在索引页中）。

表变量的内存来自数据库的 Resource Governor 资源池。

与内存优化表不同，当表变量超出范围时，所占用的内存（包括已删除的行）会被自动释放。

内存被视为数据库中 PGPOOL 单个内存消耗者的一部分。

另请参阅

Transact-SQL 对 In-Memory OLTP 的支持

通过