SQL Server 2014 has two types of In-Memory OLTP optimized tables. The default one is Durable Table. I wrote about durable table in my previous article. The other type is Non-Durable Table. We’ll see about Non-Durable Table in this article.
Just like the durable table, the schema of the non-durable table is stored in the system database in the physical disc. But, contrary to the durable table, the non durable table’s data are stored in the memory alone. So during a server crash or restart, all the data stored in the non-durable table will be lost. Thus the non-durable type of table acts like a permanent table with temporary data.
You can create a durable table using the table options in the table creation script. The first table option, you have to use is MEMORY_OPTIMIZED=ON. This will decide whether the table created should be optimized for memory or not. This table option is same for both types of memory optimized tables. The second option, you have to use is DURABILITY = SCHEMA_ONLY. This option will decide whether the table is a durable or non-durable table.
The below screen shot shows the sample query used for creating non-durable in-memory table. You can read more about memory optimized in-memory table creation and get the sample script from the article Creating Table Optimized for In-Memory OLTP.
