Why Functions Are Bad in SQL Server Where Clauses

Raised Right

---

It seems like every time I check out a server, the query plans are a nightmare. Users are freaking out, and management’s coffee is more Irish than Colombian.

Many times, the issue is that people are using presentation layer functions for relational processes. The where clause, joins, group by, and order by parts of a query.

Think about built-in string and date functions, wrapped around columns, and the problems they can cause.

These are things you should actively be targeting in existing code, and fighting to keep out of new code.

Nooptional

---

When you’re trying to get rid of them, remember your better options

• Cleaning data on input, or via triggers: Better than wrapping everything in RTRIM/LTRIM
• Using computed columns: Better than relying on runtime calculations like DATEADD/DATEDIFF
• Breaking queries up: Use UNION ALL to query for either outcome (think ISNULL)
• Using indexed views: If you need to calculate things in columns across tables
• Creating reporting tables: Sometimes it’s easier to denormalize a bit to make writing and indexing easier
• Using #temp tables: If you have data that you need to persist a calculation in and the query to generate it is complicated

Note the things I’m not suggesting here:

• CTEs: Don’t materialize anything
• @table variables: Cause more problems than they solve
• Views: Don’t materialize unless indexed
• Functions: Just no, thanks

More Work

---

Yes, finding and fixing this stuff is more work for you. But it’s a whole lot less work for the optimizer, and your server, when you’re done.

If that’s the kind of thing you need help with, drop me a line.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.