Microsoft SQL Server Declare Memory-Optimized Table Variables


Example

For faster performance you can memory-optimize your table variable. Here is the T-SQL for a traditional table variable:

DECLARE @tvp TABLE  
( 
    col1   INT NOT NULL ,  
    Col2   CHAR(10) 
);  

To define memory-optimized variables, you must first create a memory-optimized table type and then declare a variable from it:

CREATE TYPE dbo.memTypeTable 
AS TABLE  
(  
    Col1  INT NOT NULL INDEX ix1,  
    Col2  CHAR(10)  
)  
WITH  
    (MEMORY_OPTIMIZED = ON);  

Then we can use the table type like this:

DECLARE @tvp memTypeTable
insert INTO @tvp
values (1,'1'),(2,'2'),(3,'3'),(4,'4'),(5,'5'),(6,'6')

SELECT * FROM @tvp

Result:

Col1    Col2
1       1         
2       2         
3       3         
4       4         
5       5         
6       6