SQL Server 2014: In-Memory OLTP Optimized Tables

The basic of In-Memory OLTP feature in SQL Server 2014 is memory optimized table. In this article I have listed the salient features of memory optimized tables. Read the article Creating Table Optimized for In-Memory OLTP for creating In-Memory OLTP Optimized Tables.

SQL Server 2014

Note: This list of features is based on SQL Server 2014 CTP1. This community preview version of SQL Server has a lot of limitation on memory optimized tables. Hopefully the manufacturing release version (RTM) will have less limitations and more features.

Features of In-Memory OLTP Optimized Tables:

  1. There are two types of memory optimized tables. Durable Table and Non-Durable Table. (Read more about Durable Tables here. Read more about Non-Durable Tables here. Read the article Creating Table Optimized for In-Memory OLTP for creating both durable and non-durable tables.)
  2. All the data in the table will be available in the memory. (In case of durable table, another copy of the data is stored in the disk.)
  3. Multiple versions of the row is maintained in the table for concurrent reading and writing.
  4. Both TSQL and naively compiled stored procedure can be used for  fetching, inserting and updating data.
  5. Memory optimized tables are created using the table option key MEMORY_OPTIMIZED=ON. (Read more…)
  6. In SQL Server 2014 CTP1, memory optimized tables supports only non-clustered primary key and indexes.
  7. They support hash index. The option ‘bucket_count’ must be specified for index
  8. They won’t support identity column.
  9. They won’t support auto updating of statistics. statistics needs to be manually updated.
  10. Memory optimized tables won’t support Triggers. (Read more…)
  11. isolation level hint is required for selecting, inserting and updating records in memory optimized tables in a transaction.
  12. Auto-Close is not possible for databases having memory optimized tables.

Reference: MSDN.

Leave your thoughts...