Hey guys!

In today's post, I would like to share this video I just made, demonstrating, in practice, the changes we had in SQL Server 2019 in relation to estimating rows in table-type variables, where the query optimizer always estimated 1 row, regardless of the data volume, when using table-type variables, often causing an operator not optimized for the real data volume to be chosen, drastically impairing the performance of queries that used these in-memory tables.

Although this problem can be overcome using OPTION(RECOMPILE), trace flag 2453 or specific hints to use a certain operator, the 2019 version of SQL Server introduced a new concept to solve this transparently, called table variable deferred compilation, which causes the compilation of the query estimate to be postponed until the first real execution, in a behavior identical to that of temporary tables, making the number of estimated rows much closer of the real volume, instead of the estimate of just 1 line, as in previous versions.

Although I have already commented on this in the article SQL Server 2019 – List of new features and features and also in the article Webcast – Celebration 300 posts + SQL Server 2019 news (04/10/2018 – 9pm), I ended up not going into much more depth and demonstrating this in practice, which is my intention in this video.

That's it, folks!
A big hug and see you next time!