Why use a memory-optimized table variable instead of a temp table or normal table variable?
0
votes
1
answer
642
views
Table variables have a bad reputation, mostly due to cardinality estimates. However, this reputation was earned before the introduction of memory-optimized table variables. [Microsoft covers some use-cases here](https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/faster-temp-table-and-table-variable-by-using-memory-optimization?view=sql-server-ver16) , but I'm not sold on the benefits. A normal table variable will go in to RAM very quickly, so I don't see how the general benefits of in-memory OLTP will apply for memory-optimized table variables. [This article](https://web.archive.org/web/20140111080518/http://blogs.technet.com/b/dataplatforminsider/archive/2014/01/07/sql-server-2014-in-memory-oltp-memory-optimized-table-types-and-table-variables.aspx) lists some benefits that apply, but its focus is clearly on listing differences rather than benefits.
In what circumstances would a memory-optimized table variable be preferred over a normal table variable or a temp table? To keep the playing ground even, **assume that we are working in a normal stored procedure that has no reference to any memory-optimized tables.** In other words, assume that my only usage of in-memory OLTP is for table variables. Furthermore, let's assume that we are on SQL Server 2022. Assuming a version like 2014 would make answers needlessly complicated.
Asked by J. Mini
(1235 rep)
Jan 13, 2024, 05:09 PM
Last activity: Jan 16, 2024, 07:34 PM
Last activity: Jan 16, 2024, 07:34 PM