Memory Optimized Tables In SQL Server

Server-Side Caching with Memory-Optimized Tables

SQL Server 2014 introduces the In-memory OLTP engine which is fully integrated in SQL Server and allows you to create memory optimized tables. These memory optimized tables have a completely different data and index structure with no locking or latching required when you access the data. This design results in a minimal amount of waiting time and no blocking. Data for the memory optimized tables gets stored in memory in a completely different structure and there are no data pages or index pages or buffer pool for memory optimized tables.

There are two types of indexes which can be created on memory optimized tables, namely a HASH index or RANGE index. A memory-optimized table must always have at least one index, although if you create a primary key on the table, this requirement will be satisfied. You can create up to 8 indexes on a memory optimized table including the one supporting the primary key although no unique index is supported other than the primary key.

All 8 indexes can only be non-clustered indexes. These indexes don't duplicate data, but rather they just point to the rows in the chain. These indexes are not logged and don't get stored on disk.   The indexes are maintained online and created every time during recovery.

 Problem with Caching : if your data is volatile and being updated from multiple clients or applications, then caching can create as many problems as it solves because the data held at each of the individual clients can be different. In that scenario, it becomes difficult to determine what the "right" data is.