Rich Benner builds a table:
A lot of people have heard of in-memory/memory-optimized tables in SQL Server. In our experience, however, not many people are using this feature (which first appeared in SQL Server 2014) in their production environments. This introduction will explain what in-memory tables are and how to use them effectively. This post should help guide your decision about using this feature in your production environment.
For the demos below I’m using the Stack Overflow database, you can go grab a copy here. It comes in various sizes, and a small one is perfectly acceptable for this test. We’re only going to deal with 100k rows of data. Once we have the database available and attached to a test instance of SQL Server, we have a few things to do.
I would heavily caution people before going down the road of memory-optimized tables in a user database. The primary benefit is typically not read speed, but rather write speed. There are also so many restrictions around what you are allowed to do, especially if you want to take advantage of memory-optimized stored procedures. IMOLTP puts heavy restrictions on anything involving cross-database querying (and tempdb is a different database, remember!), to the point that I’ve struggled to implement it in brownfield environments. Ultimately, it’s one of those really neat-sounding features that has too many restrictions to be really useful.