Microsoft SQL Server In-Memory OLTP (Hekaton) 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
