Nah, not necessarily. SQL Server’s query optimizer behavior keeps changing with every freakin’ version. Let’s illustrate it with a simple query against the Stack Overflow Users table:

Because there are a lot of people in Ahmadabad, SQL Server gets that estimate correct, which is really important when you start joining this table to other tables, like finding their number of comments or posts:

SQL Server brought back 1856 of an estimated 1856 rows. So far, so good. But what happens when we start running functions on the parameter we’re searching for, like if it’s a parameter that we need to clean up. How will this affect our estimated number of rows:

Your knee-jerk reaction is probably to say, “FUNCTIONS BAD!” especially given that this is a case-insensitive database. But let’s test that hypothesis across time, across different database compatibility levels:

The actual execution plans tell an interesting story:

• 110 (SQL Server 2012): 1,856 estimated rows, perfect (1856 rows actually match) – so the functions didn’t matter
• 120 (2014): 65 estimated rows – worse
• 130 (2016): 65 estimated rows
• 140 (2017): 65 estimated rows
• 150 (2019): 15 estimated rows – EVEN WORSE
• 160 (2022): 1,856 estimated rows – whew, better – the functions don’t matter anymore
• 170 (2025): 1,856 estimated rows – functions still don’t matter

The “new” cardinality estimator introduced in SQL Server 2014 seemed to be unable to parse its way through the functions, even though previous versions had been able to do it. Somehow SQL Server 2019 made things even worse, and then presto: SQL Server 2022 fixed it. I’m not talking about any fancy adaptive cardinality estimation stuff either – note that I’m specifically hinting the compat level that I want in the query hints.

In the last few years, with clients on SQL Server 2022, this has led to a couple of awkward situations. I’ve been on a client call where the DBA would pull up a slow query onscreen, point at the WHERE clause, and scream, “I’VE TOLD THE DEVELOPERS A MILLION TIMES, YOU CAN’T PUT FUNCTIONS IN THE WHERE CLAUSE – BRENT, YOU TELL ‘EM!” I’d remove the functions, and the query would perform absolutely identically, with the same estimates.

What about functions on the table contents?

Above, we were running functions on the incoming parameter. Now, let’s flip it around and run functions against the table contents, but leave the parameter as-is:

The actual plans for this one tell yet a different story:

• 110 (SQL Server 2012): 163,186 estimated rows – bad, since only 1856 rows come back
• 120 (2014): 65 estimated rows – also bad, wildly underestimated
• 130 (2016): 891,751 estimated rows – swinging the pendulum into the other overestimated direction
• And all subsequent versions still use the same bad estimate as 2016

Functions on table contents generally are a terrible thing, especially since they also force SQL Server to do an index scan, processing all of the rows in the table, running the functions on every single row. It’s CPU-intensive and time-intensive.

In the Mastering Index Tuning class, I explain that to work around queries like this, you can add a computed column:

And after doing that, all of the compatibility levels’ actual plans show the same estimated number of rows: 6,547 rows, pretty doggone close to the actual 1,856 rows produced. Create an index atop that column, and:

On Enterprise Edition, all of their execution plans have nice, tidy index seeks with just 1,856 rows estimated, too. So to recap, for this particular combination of functions, are they actually bad in the WHERE clause?

• If they’re on the parameter side, and you’re on 2022 or newer compat level, probably no
• If they’re on the parameter side, and you’re on 2014-2019, probably yes
• If they’re on the table side, and you’ve put in computed columns (and even better, indexed them), probably no
• If they’re on the table side, and you haven’t put in computed columns, probably yes

This blog post can’t cover every combination of functions.

Many of SQL Server’s built-in functions have different abilities to give you accurate cardinality estimates, on different versions and compatibility levels, not to mention your own user-defined functions, not to mention that queries often combine multiple functions!

Your goal as a performance tuner isn’t to make knee-jerk reactions of “FUNCTION BAD” – but rather to use a scientific approach like this to actually test. Check to see whether the cardinality estimation is correct, rule out parameter sniffing, make sure you’re getting good index usage, and minimizing the work required by the query. You don’t wanna just point at something and yell at it, because if you’re proven wrong, then managers are gonna point and yell at you.

This post is also a great example of why I’m updating and re-recording my Mastering classes for SQL Server 2025. Most of you are still on SQL Server 2019 – which is great, I love it, and it’s stable – but in the next year or two, you’re going to be embarking on upgrade projects to 2022 or 2025. Your performance tuning journey is gonna start again, and I’m gonna be right here to help you stay up to speed.

Speaking of which, my classes are on sale this month for Black Friday! Go check ’em out.