In a previous blog, I talked about memory optimized table consumes memory until end of the batch. In this blog, I want to make you aware of cardinality estimate of memory optimized table as we have had customers who called in for clarifications. By default memory optimized table variable behaves the same way as disk based table variable. It will have 1 row as an estimate. In disk based table variable, you can control estimate by using option (recompile) at statement level (see this blog) or use trace flag 2453.
You can control the same behavior using the two approaches on memory optimized table variable if you use it in an ad hoc query or inside a regular TSQL stored procedure. The behavior will be the same. This little repro will show the estimate is correct with option (recompile).
Jack also explains how this works for natively compiled stored procedures (spoilers: it doesn’t), so read the whole thing.