InMemory：在記憶體中創建臨時表和表變數

在Disk-Base資料庫中，如果系統頻繁地創建和更新臨時表，大量的IO操作集中在tempdb中，tempdb很可能成為系統性能的瓶頸。在SQL Server 2016的記憶體（Memory-Optimized）資料庫中，如果考慮使用記憶體優化結構來存儲臨時表，表變數，表值參數的數據，那麼將完全消除IO操作的負載消耗，發揮大記憶體的優勢，大幅提高資料庫的性能。

在SQL Server 2016中，能夠直接創建記憶體優化的表類型，表變數和表值參數的數據只存儲在記憶體中；不能直接在記憶體中創建臨時表，但是，SQL Server提供一個變通方法（Workaround），通過行級安全RLS（Row-Level-Security）控制，指定只有當前Session才能訪問特定的數據，將記憶體優化表轉換為Session級別的臨時表，間接實現臨時表的局部性和自動清空特性。

一，記憶體優化表類型（Memory-Optimized Table Type）

記憶體優化表類型定義的表變數，表值參數能夠大幅提高效率（efficiency），有4個顯著的特點：

• 數據僅存儲在記憶體中，在讀寫數據時，不會產生任何的IO消耗，消除了tempdb的競爭和利用率；
• 必須有一個索引，Hash 或 Nonclustered 都行；每一個記憶體優化表必須創建一個索引；
• 只需要指定啟用記憶體優化：MEMORY_OPTIMIZED = ON，只持久化Schema；
• 必須先創建表類型，後創建表值變數；

1，創建記憶體優化表類型

CREATE TYPE dbo.TypeTable  
AS TABLE  
(  
Column1  INT NOT NULL,  
Column2  VARCHAR(10) NOT NULL,
INDEX idxName　NONCLUSTERED(Column1)
)  
WITH(MEMORY_OPTIMIZED = ON); 

2，創建記憶體優化表變數

declare @Table dbo.TypeTable 

二，創建“臨時記憶體優化表”

在Disk-Base資料庫中，局部臨時表#temp的作用域是session，創建在tempdb中，一旦session生命周期結束，系統自動回收其存儲空間。在SQL Server 2016中，不能直接在tempdb中創建記憶體優化表。要使用臨時記憶體優化表，有一個變通的方法，在DB中創建記憶體優化表，通過Row-Level-Security控制Session能夠訪問的數據行，間接實現Session級別的臨時表。

Step1，創建記憶體優化表，只持久化Table Schema

CREATE TABLE dbo.SessionTempTable  
(  
    Column1 INT NOT NULL,  
    Column2 NVARCHAR(4000) NULL,  
    SpidFilter SMALLINT NOT NULL　DEFAULT (@@spid),  
    INDEX ix_SpidFiler NONCLUSTERED (SpidFilter),  
    --INDEX ix_SpidFilter HASH (SpidFilter) WITH (BUCKET_COUNT = 64),  
    CONSTRAINT CHK_soSessionC_SpidFilter CHECK ( SpidFilter = @@spid ),  
)  
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);  
go 

Step2，創建RLS，控制用戶只能訪問當前Session的數據

CREATE FUNCTION dbo.fn_SpidFilter
(@SpidFilter smallint)  
RETURNS TABLE  
WITH SCHEMABINDING , NATIVE_COMPILATION  
AS  
RETURN  
    SELECT 1 AS fn_SpidFilter  
    WHERE @SpidFilter = @@spid;  
go

CREATE SECURITY POLICY dbo.soSessionC_SpidFilter_Policy  
ADD FILTER PREDICATE dbo.fn_SpidFilter(SpidFilter)  
ON dbo.SessionTempTable  
WITH (STATE = ON);  
go 

Step3，使用記憶體優化臨時表

• 表名替換：使用 dbo.Temp 代替 #Temp；
• 不能創建和刪除臨時表：
  • 移除代碼“create table #temp”，使用“delete from dbo.Temp”子句取代，將舊數據清空；
  • 移除代碼“drop table #temp”，建議使用 “delete from dbo.Temp” 子句，在當前Session結束前將當前Session產生的數據清空，節省記憶體空間；

雖然臨時表的使用和管理有點麻煩，但是，這點麻煩和大幅的性能提升來比，微不足道，建議使用記憶體優化表來代替臨時表，體驗飛一般的速度。

參考文檔：

Faster temp table and table variable by using memory optimization

Improving temp table and table variable performance using memory optimization

CREATE TYPE (Transact-SQL)

Indexes for Memory-Optimized Tables