0

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, 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 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.

6
  • There's a lot more to "in memory OLTP" than just being "in memory" - I suggest you have a look at red-gate.com/library/sql-server-internals-in-memory-oltp Jan 13 at 17:46
  • @MartinSmith I agree, but I fail to see the benefits for table variables of most of the benefits of in-memory OLTP (e.g. no pages).
    – J. Mini
    Jan 13 at 18:19
  • I presume you didn't download that PDF and look at things like natively compiled modules? Jan 13 at 18:36
  • @MartinSmith I must admit that I missed that it was free. I'll read more. Thanks. Anyway, I'm aware of natively compiled stored procedures. That's why I specified that "normal" stored procedure.
    – J. Mini
    Jan 13 at 18:38
  • @MartinSmith That has been a tremendously useful resource. I'll eventually make the time to read it in full. I thank you. A shame that my reading list is so long.
    – J. Mini
    Jan 13 at 20:46

1 Answer 1

-2

In addition to this resource, circumstances depend on the situation, company, and project you are involved in since memory-optimized tables are not stored on disk.

The biggest circumstance is when you want to create the table variable. For memory-optimized table variables, creation happens at deployment time, not at runtime.

If you plan to use memory-optimized tables going forward, other circumstances and limitations include but are not limited to:

  • Can't use data compression
  • Replication between other memory-optimized tables only
  • Server-level triggers are not supported
  • Database-level triggers are not supported
6
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Jan 14 at 3:26
  • Okay. In which circumstances would I prefer the memory-optimized variable?
    – J. Mini
    Jan 14 at 4:03
  • @J.Mini i updated my answer for the technical aspect. If you think my answer address your question please upvoted or mark it as the answer
    – Full Array
    Jan 14 at 16:22
  • @Community answer updated
    – Full Array
    Jan 14 at 16:29
  • But what does any of that have to do with table variables?
    – J. Mini
    Jan 14 at 16:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.