In this chapter, we have taken a look at the In-Memory OLTP engine, which was introduced in SQL Server 2014. Along the way, we have investigated how the In-Memory OLTP differs from the traditional SQL Server storage engine with respect to the concurrency model. The main difference is the ability to allow multiple concurrent users to access the data structure without relying on the pessimistic concurrency model of using locking and latching to uphold data isolation within transactions.

We continued our journey by exploring what T-SQL constructs and data types the In-Memory OLTP engine can support in the first version of the feature inside SQL Server 2014. At the same time, we were able to find out that although there are limitations, there is still a wide range of available data types and programming approaches that can be used immediately. Especially exciting is the fact that the T-SQL language receives (almost) transparent support for the powerful new capabilities. Memory-optimized tables appear at first glance to be just normal tables that somehow process data at a much higher rate than normal.

The chapter then discussed how memory-optimized tables can take advantage of different data durability options and how this can affect both performance and recoverability of data. The new storage engine provides solutions that were previously impossible in the traditional storage engine.

We rounded off the first encounter with the In-Memory OLTP engine with a discussion around how the internals of the storage engine work. We also saw how to interrogate the system using different methods to explore how data flows through the In-Memory OLTP engine.

Finally, we also had a glimpse at a useful migration assistant that can help in the preparation or identification of objects in existing databases that are possible candidates for a migration into the In-Memory OLTP engine.