In-Memory OLTP Memory Optimized Table Variables Vs Disk Based Table Variable in SQL Server

Starting SQL Server 2014, it is possible to use memory optimized table variables. These are table variables declared using a table type which is memory optimized. Memory optimized table variables have no disk footprint and don’t have PAGELATCH (like PAGELATCH_EX and PAGELATCH_SH) or LOGBUFFER waits, hence they result in faster performance compared to traditional disk based table variables.

Memory optimized table variables can be used in place of disk based table variables when possible.

Below video compares the performance difference between memory optimized table variables and traditional disk based table variables. SQLTest Tool simulates the hands-on labs for you, no registration necessary. You can practice the online hands-on example while watching the video.

Check out for additional hands-on videos.

Subscribe to our newsletter: