Speak guys!
In today's post, I'd like to share this video I just made, demonstrating in practice the changes we've had in SQL Server 2019 regarding row estimation in table-type variables, where the query optimizer always estimated 1 row , regardless of data volume, by using table-type variables, often choosing an operator not optimized for the actual data volume, dramatically impairing the performance of queries using these tables in memory.
Although this problem can be circumvented by using OPTION (RECOMPILE), 2453 trace flag, or specific hints to use a particular operator, the 2019 version of SQL Server introduced a new concept to address this transparently, called deferred table variable compilation ( table variable deferred compilation), which causes the query estimate compilation to be delayed until the first actual execution, in a behavior similar to that of temporary tables, making the estimated number of rows much closer to the actual volume, rather than of estimating only 1 row, as in previous versions.
Although I already commented on this in the article SQL Server 2019 - News and Features List and also in the article Webcast - 300 Celebration posts + SQL Server 2019 What's New (04 / 10 / 2018 - 21h), I ended up not going too deep and demonstrating this in practice, which is my intention in this video.
That's it folks!
A big hug and see you next time!
