7. In-Memory Tables

Chapter 5 introduced you to disk-based tables and how they are defined within a database. As I have mentioned in previous chapters, SQL Server tries to be as fast as possible in completing the given unit of work. The slowest component of completing the unit of work will be accessing data in tables on a disk, physical I/O. To try to reduce physical I/O and instead use logical I/O, SQL Server will try to hold data in memory based on its own calculations on what data should be held and how recently the data was last accessed. It would be better for you to be able to define to SQL Server which tables are so important that the data they hold should be permanently held in memory. This is where in-memory tables come in to their own. Up to and including SQL Server 2012, the only way to achieve holding data permanently within memory was to pin a table to being held in memory. SQL Server used disk-based optimizations as the table was on disk, but from SQL Server 2014 it is possible to define a table to permanently reside in-memory, and it now also has its own database optimized engine to fully utilize in-memory held data.