SQL Server 2014 introduced In-Memory optimized tables with limitations that made them impractical in most situations. The release of SQL Server 2016 has added additional functionality and improved integration with SQL Server’s Database Engine that make it worth considering over temporary tables to improve performance, especially with Online transaction processing (OLTP) database applications.
With memory-optimized tables, the entire table resides in memory and data within the table is accessed from and written to memory, eliminating I/O contention and improving data retrieval. The creation of a memory-optimized table by default is durable, involving limited disk I/O activity for transaction logs to allow for data recovery in case of a server failure. However, they can be defined as non-durable where no I/O activity is required on tables where data is not critical like staging processes.
Data within memory-optimized tables can be accessed through Transact-SQL and the data is interoperable with standard disk-based tables. A memory-optimized table must have at least one index and indexes are always stored in memory even for non-durable tables. In case of a server failure or restart, the indexes are rebuilt as the data is read into memory.
If you are looking to squeeze that extra performance out of your database it is worth taking a look at using In-Memory optimized tables.