Microsoft SQL Server 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):

  1. Create a new SCHEMA_ONLY memory-optimized table with the same schema as the global ##temp table
    • Ensure the new table has at least one index
  2. Change all references to ##temp in your Transact-SQL statements to the new memory-optimized table temp
  3. Replace the DROP TABLE ##temp statements in your code with DELETE FROM temp, to clean up the contents
  4. Remove the CREATE TABLE ##temp statements from your code – these are now redundant

more informations