With SQL Server 2014 we were introduced to in memory tables, and specifically the ability to use non-durable in memory tables. These non-durable tables, are special case tables. The schema will exist after the database engine is rebooted, however the table will be empty after the server is rebooted.
These tables are great for filling a specific need, and are useless for other needs. The need that these tables fill is processes such as staging tables in data warehouses, logging tables, and that’s just about it. These tables are fantastic when loading data where the data can be lost, or when the data can be recreated on the fly. This is why they are fantastic for loading tables for data warehouses because by their nature the data being written to loading or staging tables can be reloaded from the source system at a moments notice.
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.
So as you continue to look into SQL Server 2014 and as you begin to work with the in memory tables, be sure to understand the difference between the durable and non-durable tables and when to use each one.
Denny
