Durable vs. Non-durable tables: In-Memory OLTP

Hello followers Today i will explain very important point Durable vs. Non-durable tables SQL Server 2014 when come for us with In-Memory  Feature come with 2 Kind of tables Durable and Non-durable tables The default one is Durable Table. One cool feature besides the durable in-memory tables, is the possibility to create non-durable in-memory tables. As you realize, you can get a lot of performance improvement with the in-memory solution, but what is the benefit with non-durable tables as the data will be lost in case of failure? Well, one option is to use non-durable in-memory tables as staging tables in your ETL solution as you don’t care about the staging data in case of a system crash. do you have Temporary data ? do you have data you are OK to lose the data due to SQL Server restart at this time you can think in Non-durable tables, 

Memory-Optimized-Table come with two options (DURABILITY = SCHEMA_AND_DATA) , (DURABILITY = SCHEMA_ONLY)

Durable Table          : DURABILITY = SCHEMA_AND_DATA

Non-durable tables :DURABILITY = SCHEMA_ONLY

Complete the post to know more about the Scripts used in the DEMO

Why would I want to use a non-durable in memory table?

These non-durable tables, are special case tables after the database engine is rebooted he table will be empty but this tables is great for filling  very very fast so this tables is very fantastic for  staging tables in data warehouses, logging tables,For production tables within an OLTP system these non-durable tables are a really bad idea. If you were to use non-durable tables in a production OLTP database, under most situations if the data was lost this would end up being a really bad idea because data would be lost when the SQL Server instance is rebooted.

Let’s go for demo to know How non-durable in memory table is faster than durable in memory table and how the data will lose after the engine rebooted.

[sql]

USE [master]
GO

/****** OBJECT: Database [SQL2014_WorkShop] Script Date: 04/07/2014 04:10:11 PM ******/
CREATE DATABASE [SQL2014_WorkShop]
 CONTAINMENT = NONE
 ON PRIMARY
( NAME = N'SQL2014_WorkShop', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSERVER2014\MSSQL\DATA\SQL2014_WorkShop.mdf' , SIZE = 4288KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
 FILEGROUP [SQL2014_WorkShop_mod] CONTAINS MEMORY_OPTIMIZED_DATA DEFAULT
( NAME = N'SQL2014_WorkShop_mod', FILENAME = N'c:\SQL2014_WorkShop_mod' , MAXSIZE = UNLIMITED)
 LOG ON
( NAME = N'SQL2014_WorkShop_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSERVER2014\MSSQL\DATA\SQL2014_WorkShop_log.ldf' , SIZE = 1072KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

[/sql]

Durable in memory table

[sql]

Create Table Customer
(
Customer_ID INT NOT NULL Primary key nonclustered Hash WITH (bucket_Count = 1000000),
FirstName Nvarchar(200) NOT NULL,
lastname Nvarchar(200)NOT NULL
)
With(Memory_optimized=on,Durability=SCHEMA_And_DATA)

[/sql]

Non-durable in memory table

[sql]

Create Table Non_Durable_Customer
(
Customer_ID INT NOT NULL Primary key nonclustered Hash WITH (bucket_Count = 1000000),
FirstName Nvarchar(200) NOT NULL,
lastname Nvarchar(200)NOT NULL
)
With(Memory_optimized=on,Durability=SCHEMA_ONLY)

[/sql]

i will insert 100,000 Record in Durable table (Customer) and it will take 11 SEC

[sql]

set nocount on
go
DECLARE @counter INT
SET @counter = 1

WHILE @counter <= 100000
 BEGIN
 INSERT INTO dbo.Customer
 VALUES (@counter, 'mustafa','Elmasry'),
 (@counter+1, 'Amro','Silem'),
 (@counter+2, 'Shehab','Elnagar')

SET @counter = @counter + 3
 END

[/sql]

now i will do the same Exercise on non  Durable table and 100,000 will take ZERO SECOND:

[sql]

set nocount on
go
DECLARE @counter INT
SET @counter = 1

WHILE @counter <= 100000
BEGIN
INSERT INTO dbo.Non_Durable_Customer
VALUES (@counter, ‘mustafa’,’Elmasry’),
(@counter+1, ‘Amro’,’Silem’),
(@counter+2, ‘Shehab’,’Elnagar’)

SET @counter = @counter + 3
END

[/sql]

Now Check the Count for the both tables

Now Restart your SQL Server Engine or Alter the Database SQL2014_WorkShop to be in the Offline mode then Online Mode and Check again the Count of both tables

[sql]

Use Master
go
ALTER DATABASE SQL2014_WorkShop SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE SQL2014_WorkShop SET ONLINE
GO

[/sql]

Check now the Count of data in both tables you will found No Data in Non-Durable in memory table

[sql]

Select Count(1) from Non_Durable_Customer
Select Count(1) from Customer

[/sql]

So at the End take care from Durable table and non-Durable table in memory Optimized table you should know when you need to use this or this if you don’t care about the data loss you can use non-Durable table in memory
table.

this not the End if you need to know more Secrets, more Surprise , More Workaround in

SQL Server 2014 don’t close your email Still follow me Mostafa Elmasry 

to be the first one know this new Secrets in SQL Serve 2014  and

Follow this Link (In love of SQL Server 2014) to know all our blogs in SQL Server 2014 To know what is the new in SQL Server 2012 or 2014 to View all my tips

See you in the next blog Post

Follow ME

View all my tips , LinkedIn , Website , Slideshare ,Youtube Channel