Durable Table is a type of memory optimized table. It is the default In-Memory optimized OLTP table. The other kind of memory optimized table is Non-Durable Table. In this article I will give an overview about durable table and to create durable table.
The schema of the durable table is stored in the system database in primary file group, just like the regular tables. The data are stored in the MEMORY OPTIMIZED DATA filegroup. Creating a memory optimized durable table is simple. You can use the regular table creation script with MEMORY_OPTIMIZED=ON table option to create a durable table. You can read more about memory optimized table creation here. Currently in SQL Server 2014 CTP1, you can use only non crusted hash index in durable table. May be, this will change in the RTM version.
Once the SQL Server 2014 is restarted, the data in the durable table is loaded to the memory. the online transaction processing is done on the data loaded to the memory. So there will not be any IO bottleneck for the transaction processing. All the DML operations on the durable tables are handled in the memory. There is a background thread which reads the log of the durable table and persists the data to the check point files. The background thread is called offline check point.
Data stored in durable memory optimized table can be fetched using a naïvely compiled stored procedure or a regular TSQL statement.
Below is a sample script for creating durable table. You can read more and copy the sample script from the article Creating Table Optimized for In-Memory OLTP.
Read more about memory optimized table in the article In-Memory OLTP Optimized Tables.
Currently, I’m exploring the SQL Server 2014 CTP1. I will write more about SQL Server 2014 in the coming days.