Microsoft SQL Server In-Memory OLTP (Hekaton) Memory-Optimized Table Types and Temp tables
Example
For example, this is traditional tempdb-based table type:
CREATE TYPE dbo.testTableType AS TABLE
(
col1 INT NOT NULL,
col2 CHAR(10)
);
To memory-optimize this table type simply add the option memory_optimized=on, and add an index if there is none on the original type:
CREATE TYPE dbo.testTableType AS TABLE
(
col1 INT NOT NULL,
col2 CHAR(10)
)WITH (MEMORY_OPTIMIZED=ON);
Global temporary table is like this:
CREATE TABLE ##tempGlobalTabel
(
Col1 INT NOT NULL ,
Col2 NVARCHAR(4000)
);
Memory-optimized global temporary table:
CREATE TABLE dbo.tempGlobalTabel
(
Col1 INT NOT NULL INDEX ix NONCLUSTERED,
Col2 NVARCHAR(4000)
)
WITH
(MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_ONLY);
To memory-optimize global temp tables (##temp):
- Create a new
SCHEMA_ONLYmemory-optimized table with the same schema as the global##temptable- Ensure the new table has at least one index
- Change all references to
##tempin your Transact-SQL statements to the new memory-optimized table temp - Replace the
DROP TABLE ##tempstatements in your code withDELETE FROM temp, to clean up the contents - Remove the
CREATE TABLE ##tempstatements from your code – these are now redundant
