SQL Server Portal

Memory optimized table is a new concept to maintain records in table with high performance. I already discussed this new type of table earlier in my article. So, I kept on researching for this new feature and came across an issue. The issue is, I once created a memory optimized table and inserted few records in it and did other research work on the test database and once I was done, I just shut down my machine. Next morning when I tuned on my machine, I found no data in the memory optimized table. First I thought I deleted the data by mistake and forgot it. So I repeated the process but the following morning same thing happened to me again. I could not comprehend what on earth was going on !!  I was puzzled and started doing my research and finally resolved it.

Let me explain it step by step. (Never apply these steps on production database)

Step 1 :
Create a memory optimized table in a memory optimized table enabled database.

--This script is compatible with SQL Server 2014 and above.
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

Step 2 :
Insert records in the memory optimized table.

--This script is compatible with SQL Server 2014 and above.
USE hkNorthwind
GO
INSERT INTO tbl_Product_Master VALUES (1, 'SQL Server 2012',GETDATE())
INSERT INTO tbl_Product_Master VALUES (2, 'SQL Server 2014',GETDATE())
GO

Step 3 :
Browse the memory optimized table.

--This script is compatible with SQL Server 2005 and above.
USE hkNorthwind
GO
SELECT * FROM tbl_Product_Master
GO

Step 4 :
Now either restart the database server or shutdown your test machine and turn it on.
Never do this exercise on production server without taking proper precautions.
Browse the table again.

--This script is compatible with SQL Server 2014 and above.
USE hkNorthwind
GO
SELECT * FROM tbl_Product_Master
GO

Step 5 :
Opsssss, now you can see the data gone and the reason behind this is, just a parameter that we passed at the time of memory optimized table creation and this is DURABILITY because if you make DURABILITY = SCHEMA_ONLY the schema will be durable but not the data. Once you restart the database server, you will lose your data. Remember, whenever you create a memory optimize table, to  keep the durable data (permanent data). Always use WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA).

Note : If you create the memory optimized table without passing DURABILITY parameter, by default it will be DURABILITY = SCHEMA_AND_DATA.

Lets recreate the memory optimize table. Given below is the script. Do the same process again without step 1 and your data will remain with you.

--This script is compatible with SQL Server 2014 and above.
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_AND_DATA)
GO

Please let me know if you come across this issue and its resolution.

Reference : MSDN