I set the compatibility mode as follows: ALTER DATABASE AdventureWorks2012 SET COMPATIBILITY_LEVEL = 140 This time I’m just going to compare a table variable running under compatibility mode 140 (SQL 2017) with one running under compatibility mode 150 (SQL 2019). My first test last time was just to show how cardinality estimation was better for a temp table vs.
One thing to note though it that I’m running these tests on a different, much less performant box than I did last time, so execution times will be longer. I’m going to re-use my examples from the previous post (as well as some of the images captured).
TL DR It looks like table variables are no longer a problem. So, when I saw the above 2019 feature I thought I’d better investigate and update what I’m telling people. This wasn’t new information when I wrote it, but bad performance due to the use of table variables remained such a common anti-pattern that I thought it was worth stressing again. One of the most popular posts on my blog last year was where I pretty much suggested that people not use table variables: Reading the new features for SQL 2019 I spotted this:
0 kommentar(er)
