Improvements In Table Variable Performance In SQL Server 2019

Matthew McGiffen tries out SQL Server 2019 to test a scenario where table variables were giving poor estimates in prior versions:

One of the most popular posts on my blog last year was where I pretty much suggested that people not use table variables:

Think twice before using table variables

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.

So, when I saw the above 2019 feature I thought I’d better investigate and update what I’m telling people.

TL;DR It looks like table variables are no longer a problem.

Read the whole thing.  This has the potential of changing long-standing advice going back a decade regarding table variables.

Related Posts

Tuning Azure SQL Database

Tim Radney walks us through some of the tools we have available to tune Azure SQL Databases: Many instance-level items that you have been used to configuring on full installations are off limits. Some of these items include:– Setting min and max server memory– Enabling optimize for ad hoc workloads– Changing cost threshold for parallelism– […]

Read More

SQL Server and Ubuntu 18.04

Randolph West confirms that SQL Server on Linux will run on Ubuntu 18.04 even though it is not (yet) supported: Although these screenshots show SQL Server 2019 preview CTP 2.3, this also applies to SQL Server 2017 on 18.04.2, because that’s what I had installed before upgrading the SQL Server version. However, as my friend […]

Read More

Categories

September 2018
MTWTFSS
« Aug Oct »
 12
3456789
10111213141516
17181920212223
24252627282930