When the In-Memory OLTP engine was introduced in SQL Server 2014, Microsoft announced that the maximum size of any memory-optimized table was 256 GB. This limit was reached using internal testing at Microsoft and was introduced to ensure the stability and reliability of the feature in production environments. The main decision here involved the design of the storage subsystem, particularly around the checkpoint file pairs. There was a hard limit of 8,192 checkpoint file pairs, each capable of storing 128 MB of data, however the limit of 256 GB was not a hard limit, and was rather a suggested maximum.

The memory requirements for a memory-optimized table are dynamic. This dynamism is grounded in the memory requirements for merging data changes into memory-optimized tables (inserts, deletes, and updates) and the follow-on redundancies of versioned rows and rows waiting for garbage collection.

When SQL Server 2016 was released, Microsoft quoted a maximum size of 2 TB for a memory-optimized table. Again, this was due to testing and reliability measurements inside the development team at Microsoft. The development team has re-architected the storage subsystem for the checkpoint file pairs, removing the 8,192-pair limit, effectively allowing a memory-optimized table to have no upper size limit. In other words, as long as you have memory available, the table can grow. This does not mean that any and all tables should be memory-optimized, but it does mean that we are now able to consider using the In-Memory OLTP engine to store any size of table that we can design.