Forgery
One of the most common things I see when working with vendor apps are queries that need to filter or join on some expression that traditional indexing can’t make efficient.
B-tree indexes only organize the data as it currently exists. As soon as you perform a runtime manipulation on it in a join or where clause, SQL Server’s optimizer and storage engine have far fewer good choices to make.
This comes back to a couple rules I have when it comes to performance:
- Anything that makes your job easier makes the optimizer’s job harder
- Store data the way you query it, and query data the way you store it
Presentation Is Everything
SQL Server has many built in functions that help you easily manipulate data for presentation. It also lets you write a variety of user defined functions if the existing set don’t do exactly what you want, or you have different needs.
None of these functions have any relational meaning. The built in ones don’t generally have any additional side effects, but user defined functions (scalar and multi-statement) have many additional performance side effects that we’ll discuss later in the series.
This practice violates both of the above rules, because you did something out of convenience that manipulated data at runtime.
You will be punished accordingly.
Snakes
These are the situations you want to avoid:
- function(column) = something
- column + column = something
- column + value = something
- value + column = something
- column = @something or @something IS NULL
- column like ‘%something’
- column = case when …
- value = case when column…
- Mismatching data types
For a lot of these things, though, you can use a computed column to materialize the expression you want to use. They’ve been around forever, and I still barely see anyone using them.
There are a lot of misconceptions around them, usually that:
- They cause blocking when you add them (only sometimes)
- You can’t index them unless you persist them (you totally can!)
Known
There are some interesting things you can do with computed columns to make queries that would otherwise have a tough time go way faster. To make it quick and easy for you to learn about them, I’m making videos from my paid training available here for you to watch.
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.