How To Write SQL Server Queries Correctly: Case Expressions
Thanks for watching!
Video Summary
In this video, I dive into the intricacies of case expressions in SQL Server queries, explaining why they can be problematic for performance tuning and optimization. I share insights from my experience as a database professional and highlight how seemingly innocent use of case expressions can lead to complex query plans and significant execution times. By walking through examples and referencing Aaron Bertrand’s blog post on the “Dirty Secrets of the Case Expression,” I aim to help you understand why it’s crucial to be mindful of where and when you use these constructs in your queries, especially in joins and where clauses.
Full Transcript
Erik Darling here with the old Darling Data. Having a lot of fun. A lot of fun. I don’t know how anyone doesn’t have fun with databases. They’re just the most amusing things one could possibly conceive of. Barrel of laughs. Today’s video, we’re going to get ever closer to the close of our series about how to write queries correctly. And in this one, we’re going to cover case expressions. Not case statements. Case is not a statement. Case is an expression. You’re just going to have to deal with that fact. Or else, I don’t know, I’ll drop a bag of hammers on you. If you’re that gung-ho about calling case a statement, it might have no effect on your skull. It’s probably rock solid anyway. If you like this content, and you’re like, wow, where would I be without this content? Well, for four bucks a month, you can support this content. And I can, you know, carry the two. I’ll make about about $3.10 after taxes on that.
Give or take. So, that’d be nice. If you don’t have $4 to turn into the alchemy of taxation to hand over to me, you can like, you can comment, you can subscribe. And there’s no tax on that. That is free love. If you are in need of SQL Server Consulting. If the things that I say in these videos resonates with you, and you think that perhaps my wit and wisdom would be of some benefit to the SQL Servers that you care and feed, well, you can hire me to do all sorts of things. And as always, my rates are reasonable. I also have training. And you could have that training for about $150 for the rest of your life. Not a subscription. You can go to that link and plug in that coupon code. And through, again, magical transformations. Actually, reverse taxation. You actually get money off of things. I guess that’s a tax on me.
Boy, this is a rough mental awakening for me. Me giving you discounts is a tax on me. That’s messed up. But you can get all of my training for, yeah, that money, that URL, that code. It’s amazing. No upcoming events because end of year, family, things like that. I’ll be around in 2025. With all that out of the way, let’s talk about these here case expressions. Now, one thing that you should know off the bat is that green screens later in the day are hard. That’s why I have strange things happening behind this armpit. Do not look behind the armpit.
Is that some things internally, even though they appear to be built-in functions that SQL Server has, the things that turn pink when you type them. Some of those things are just wrappers for case expressions. Translate is not one of them, though. When I was first looking into this, I half expected translate to be a case expression, but it is not. If we look at the query plan and we look at the compute scalar, the defined thing in here will not say anything about a case expression.
But if we dig into some of these other things, like if, and we look at the execution plan here, and we look at the compute scalar, we will see that internally, if, is just doing a case expression. You can see where that starts right here. So if is just a case expression. This is one of the reasons why when I start having to warn people about using functions and where clauses and joins, I also need to warn them about using case and where clauses and joins, because guess what?
They both have the same performance problems. It’s amazing. It doesn’t matter if you explicitly write case. Some of the functions that you might write will use case anyway. Behind the scenes, they’ll just magically crop up there for you. Another one of them is coalesce. If we do this, and we look at the execution plan, and we look at the predicate that gets applied to the table, that is a case expression too.
Case when is not null, then, blah, blah, blah, end. See? Coalesce. Inside, just a case expression. Choose, similarly, is a case expression. If we look at the query plan here, and we will look at the compute scalar, we will once again see the defined values show us a case expression.
So, if you can, because you’re the one writing queries, you should probably not put things like if, and coalesce, and choose, and your where clause, because they are case expressions, and they will have performance problems. Other built-in functions, like is null, and replace, and stuff like that, they are not case expressions inside, but they have very similar performance problems.
All of a sudden, using indexes becomes difficult for a SQL Server. Aaron Bertrand, who, I don’t know, if you ask me, I don’t talk to him enough. If you ask him, I talk to him too much.
So, there’s two sides to every coin. Either, you know, I don’t know what Canadian coins are called. But, these, he has a blog post anyway, called, The Dirty Secrets of the Case Expression.
Thank you, Aaron, for not calling it a case statement. And this is an example that he has in his blog post. There are more if you use the search engine of your choosing. I don’t care what it is.
And you search for Aaron Bertrand, Dirty Secrets of the Case Expression. You will find his post. This is the most amusing one that I think exists on there. Is this, when you declare a variable called i, and you say, case when i equals one, then one, else the minimum of one divided by zero.
And you get a divide by zero error, even though this part never should have been explored, right? Because this equals one. If you rewrite that a little bit, and you say, else select min, that will work.
Case figures that one out. Case doesn’t go that far along in that. But it’s an interesting thing, where case does not always short circuit where and when you think it might. All sorts of things can happen in there.
Now, let’s talk about performance, though. Because that’s what we care the most about. We are, for the most part, performance tuners. We’re pretty good at it at this point, I think.
At least I hope, anyway. That’s what we get paid for. I see a lot of queries with a lot of problems. And a lot of them look something like this.
And notice that there’s a join with a case expression here. All right, so there’s a, we’re going to join depending on if this, right? Remember, so like if one equals, ah, gosh darn it.
If one equals case when this, then one. And when this condition, then one. But if we don’t meet those conditions, then zero. And then another thing where one equals case when these things are, these conditions are true than one.
Else zero end. And I’m not actually going to run this query now. Because we’re already around the eight minute mark.
And this query takes a full minute and one second to execute. The query plan is not terribly interesting. Just note that everything is fine until we try to apply that expression right here. All right, a whole bunch of bad things get involved at the join.
And this portion of the plan eats up the majority of the time. It would be nice if I could draw like different shapes with ZoomIt. But I guess making two rectangles isn’t going to, you know, isn’t that much of a challenge for me.
It’s not that difficult. It would just be nice if I could like draw better in general. If I could draw better, I would probably have a different career, honestly.
But this query plan is, of course, a disaster. And you should not emulate this kind of query. This query is no good.
For the sake of those who may, I don’t know, be watching and not listening, maybe you take visual cues better than you take verbal cues. We are going to say no.
No. No. If I was better at art, I would make this a skull. But I’m not very good at art.
So, er, er, er. And we’ll get some things here that hopefully don’t look too much like other things. Eh.
We’re getting good at this. We’re getting better at this. Every day. Every day. We want to avoid this. This is bad. Okay. This is not what you want to do with your queries. This is frowny face.
Bad for performance. But, but. Okay. If all this is not enough clue, I don’t know what to do for you. We are, we are, I’m out of ideas.
So, one way that you could rewrite this query would be something like this. You don’t have to do this. But this, to me, this just looks kind of nice. We’re going to put the query, the predicates that we care about for the users table in here.
Right? Because that’s good. We care about that. And we’re going to cross apply to a union all. And if you are, you’ve been following this series, separating out queries like this.
Very common. Very useful query pattern. Even if it doesn’t solve the precise performance problem that you’re after, it can certainly illuminate you as to why you are having a performance problem with one or both of these queries or sets of, the set of predicates in here.
Perhaps it’s an indexing issue or something else that’s just driving things amok. But when we run this query, this thing will let me highlight things. This returns instantly.
Right? We no longer have to wait a very long time. We no longer have a giant parallel plan with a spool in it and join predicates figured out at the, or rather, predicates figured out at the join clause.
We have an apply nested loops plan. Again, we can tell that the apply nested loops kicked in because we have this outer references thing in the nested loops join, which means that our join predicates were sent out as seek predicates to here and here.
So, I mean, obviously that’s the, somewhat obviously, because it’s the same index name. We hit the same index twice and seek into it and all is well. And we get the, back the result that we care about immediately. So, when you’re writing queries, putting things like case expressions and joins and where clause may seem like a convenient vehicle for you to express the logic that you care about, but it is not, often not a very convenient vehicle for SQL Server to optimize.
Again, this is really, when you’re writing queries where performance matters, which you should write every query like performance matters because someday it might, even if it doesn’t today, someday performance might be of critical essence, critical importance to this query or to whoever’s running this query.
And you don’t want it to be slow. And you don’t want like one of those comments up at the top where it has your full name and a date and like all that stuff in there. And where you’re like, you know, you make notes about things that you swear work, but don’t actually work.
So, these may seem very convenient for you, but these are very bad for SQL Server. Much better ways exist to write queries that SQL Server can interpret into a much better, much faster execution plan for you.
So, one thing that I talked about at the, well, not at the beginning of this post, obviously, because this is, this is the culmination of several things is handling, like using or. So, the case, this video on case expressions really ties in with the, the two videos on where, where, where clauses with or in them and join clauses with or in them.
Uh, they have very similar performance implications and sometimes even query plan implications. So, one thing that I haven’t covered in these that I’m going to cover in a longer format about dynamic SQL is how to handle cases where you want to, um, have optional parameters passed in, uh, to a store procedure.
Sometimes they are null. Sometimes they are used and there are different ways to sort of deal with that. Um, but please, for, at least for the time being, keep case expressions out of your joins and your where clauses and, uh, be, be careful, be very diligent in how you write case expressions, even in your select list, because they may not shortcut when you expect them to.
SQL Server may do some additional exploring in those case expressions that you are not aware of until something weird happens. So, be careful there.
Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in the next video on, uh, CTE. I know that I already did one in this series, but, um, the, the next two videos on common table expressions and output are going to be literally everything you need to know about CTE and output.
Do not buy anything from anyone purporting to teach you about case expression, about common table expressions and output. There is nothing worth paying for to learn about them. Okay?
Spend no money on these things. They’re not, they’re not worth it. Anyway, I will see you in those videos. Au revoir.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% 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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.