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.
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:
- 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.)
- 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.)
- Multiple versions of the row is maintained in the table for concurrent reading and writing.
- Both TSQL and naively compiled stored procedure can be used for fetching, inserting and updating data.
- Memory optimized tables are created using the table option key MEMORY_OPTIMIZED=ON. (Read more…)
- In SQL Server 2014 CTP1, memory optimized tables supports only non-clustered primary key and indexes.
- They support hash index. The option ‘bucket_count’ must be specified for index
- They won’t support identity column.
- They won’t support auto updating of statistics. statistics needs to be manually updated.
- Memory optimized tables won’t support Triggers. (Read more…)
- isolation level hint is required for selecting, inserting and updating records in memory optimized tables in a transaction.
- Auto-Close is not possible for databases having memory optimized tables.
Reference: MSDN.