SQL Server Portal

Recently, I was doing some research on memory optimized table and I came across a situation where I needed to keep some temporary data in the tables. So I thought of creating temporary table. Now I have two choices, either to go for disk based temporary table or to go for memory optimized temporary table.
I decided to create a memory optimized temporary table.

Given below is the script.

USE hkNorthwind
GO
--DROP TABLE #tbl_Product_Master
--GO
CREATE TABLE #tbl_Product_Master
(
[Product ID] INT not null primary key nonclustered hash
with (bucket_count = 1024),
[Product Name] NVARCHAR(100),
[Creation Datetime] datetime
) WITH (MEMORY_OPTIMIZED = ON)
GO
--OUTPUT

Msg 10770, Level 16, State 92, Line 11
The feature ‘temporary tables’ is not supported with memory optimized tables.

Opssssssss error….

If I am not wrong, you cannot create a memory optimized temporary table because this feature is not supported by memory optimized table. So I started my research and tried to find out the work around that can help me create a memory optimized temporary table. Finally I came to know how to create a temporary table in memory optimized table.

Given below is the script.

USE hkNorthwind
GO
--DROP TABLE tbl_Product_Master
--GO
CREATE TABLE tbl_Product_Master
(
[Product ID] INT not null primary key nonclustered hash
with (bucket_count = 1024),
[Product Name] NVARCHAR(100),
[Creation Datetime] datetime
) WITH (MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY)

GO
--OUTPUT

As you can see, I just added one argument (DURABILITY = SCHEMA_ONLY) at the time of memory optimized table and because of this argument, the table is converted to a temporary table.

Let me know if you came across this issue and its solution.

Reference : MSDN