What’s The Point of 1 = (SELECT 1) In SQL Server Queries?
Thanks for watching!
Video Summary
In this video, I delve into the age-old question of why one might see `1 = SELECT 1` in SQL Server queries, a topic that garners about 70 to 80 comments per week. I explain that this snippet is often used to avoid trivial plans, which can hide important optimizations or cause confusion due to simple parameterization kicking in. By incorporating `1 = SELECT 1`, we ensure the query optimizer makes full cost-based decisions, leading to potentially more optimized execution plans. The video walks through examples where `1 = SELECT 1` is crucial for demonstrating certain behaviors and clarifying complex queries, especially when presenting or debugging issues. I also discuss how this technique can help in maintaining clarity during demos and presentations by ensuring the exact query text used matches what was executed, avoiding misunderstandings and confusion among viewers.
Full Transcript
Erik Darling here, fresh from a full day of celebrating freedom, and back to work. For you, because, I don’t know, do I work for you? I might. I might work for some of you who watch. Maybe not enough. Maybe I should work for more of you who watch. That’d be nice. Then I could just do this all day, and I wouldn’t have to, like, do stuff over there on the computer you can’t see. So that’d be cool. I don’t know. Anyway, we’re gonna, in this video, I’m gonna answer a question that I answer 70 to 80 times a week. And it is, why do you have 1 equals select 1 in your queries? And the funny thing is that everyone who asked me that question asked me that in a comment. What amuses me, I suppose, is that if you were to type what’s the point of 1 equals select one into any search engine, even dumb Bing can find it. You would find my New York Times bestselling blog post called, what’s the point of 1 equals select 1 in SQL Server queries? And you would see, you would see, you know, the same thing.
nearly the same text instead of demos appearing in this SQL Server Management window in handy, easy-to-read blog format.
So in this video, I’m going to read my blog post to you, and hopefully you will watch it, and hopefully this will be available as a secondary resource for anyone who looks at one of my demos and is puzzled by the presence of 1 equals select 1.
So here we go. We are already using the correct database. I believe we have already dropped all of the indexes we can possibly drop, so we’re good there, right?
That’s excellent news. We’re in good shape, you and me. So the main reasons for using 1 equals select 1 in SQL Server queries is to avoid two things.
One is a trivial plan, because trivial plans can hide all sorts of, or preclude the inclusion, academics, of certain optimizations that you only get when the optimization level is full.
So that’s one good reason. And I often use it in my demo queries because I want to write the simplest possible demo query to show the behavior I want you to see is possible.
The trouble is that sometimes when the simplest query doesn’t work out, either because the trivial plan does not get me the optimization thing that I want, or people see the simple parameterization thing kick in and get very confused.
Like, is that forced parameterization? Like, what’s wrong with your database? Is it broken?
Why is that parameter there? And it’s kind of funny. In some ways, I think that writing slightly more complicated queries would be less distracting to the casual viewer than just putting 1 equals select 1 in there to do what I want.
The trouble with writing more complicated queries is they become more prone to failing. The demo gods are harsh gods.
They, I don’t know, they hate me sometimes. So, yeah, there we go. Anyway, so some examples of, you know, things like I’m talking about.
1 equals select 1. Important stuff. Things you should know. And I’m not suggesting that you should put in 1 equals select 1 in all of your queries, but if you’re writing demos or you’re just testing stuff out, it can kind of be a neat thing to see if it changes anything.
So, here’s an example where I’m going to run these two queries, and we’re going to look at these two execution plans. And, of course, as promised, this query up here is simple parameterized.
You might be able to tell by looking at some of this stuff and realizing Erik Darling is not a dork and does not put square brackets on around absolutely everything in his query. And Erik Darling is the kind of guy who properly uses as when aliasing.
Aliasing things. Asleucing things. So, this query is clearly not exactly the one that I wrote.
It’s also got a little parameter over here way at the end called at 1. Fascinating. Absolutely fascinating stuff.
You might be even more fascinated to learn that this is a trivial plan. All right. You can see the optimization level trivial here. And we can see kind of a strange thing with the parameter list where SQL Server inferred the data type of the number 2 as a tiny int. If we were to write queries with a number 1 higher than the max of tiny int, small int, int, and big int, we would see the data type change for the parameter of each one of these.
At some point with the int max and the big int max, it starts using weird decimal types, though. It doesn’t explicitly use big int.
Sorry. So, that’s one reason why. Right? So, we can see that SQL Server clearly does slightly more thoughtful optimization with the second query that has 1 equals select 1 on it because the second query, of course, SQL Server says, Hey, have you thought about adding an index to make this faster?
Now, you know, 188 milliseconds isn’t terribly slow. Fine. I know.
But sometimes it’s the thought that counts. You might also notice that this query is written much more in the style of Erik Darling, where we have a proper as, for our alien, as-lesy-fiziting, and we don’t have dorky square brackets around things that don’t need them.
Right? So, cool. SQL Server gave me my query back. Stop enforcing its stupid query formatting on my beautifully written and formatted query.
Bug off, SQL Server. Sought off, Swampy, as a wise man once said. So, what gets fully optimized?
Right? Aside from, like, you know, 1 equals select 1, all sorts of things get fully optimized, but generally they require SQL Server to have to make some sort of cost-based decision about what the cheapest way to do something is.
So, join, subqueries, aggregations, ordering without a supporting index, lots of stuff that, you know, where all of a sudden SQL Server has to do more than figure out, I just have to select some rows from one table where this column equals a thing.
Easy peasy. I don’t have to, there’s not a lot of cost-based decision making in that process, unless there are multiple indexes involved. Of course, your tables all have multiple indexes involved.
So, the likelihood of you needing to write 1 equals select 1 and, like, a production query are pretty low. So, let’s look at these two queries. Right?
We’re going to select the top 1,000 IDs grouped by ID, which, of course, is meaningless because ID is all, what do you call it, unique values. It is the clustered primary key of the table.
And the reputation column, of course, is very ununique. Very ununique as a column. And so, you know, these obviously return different results because we’re doing different things.
But this query right here, if we look at this, we are with a trivial plan once again because there is no cost-based decision to make. This one down here is not a trivial plan. This is a fully optimized plan.
And the reason this one is fully optimized is, of course, because SQL Server had to choose what to do in here. Right? This operator represents a cost-based decision. And this operator is why.
SQL Server was like, oh, I’m going to fully optimize this thing because I need to think about how to group this column. Am I going to use a stream aggregate? Am I going to use a regular hash match?
Do I want to use a partial aggregate first? And at the end, it shows a hash match flow distinct. Right? That was apparently the cheapest one. So, happy times there.
Happy, happy times. So, one reason, or it’s a good way to put this. One situation where using 1 equals select 1 isn’t necessary is if you have an index, if you have multiple indexes on a table.
Now, this index right here has absolutely nothing to do with this query. We’re not.
Like, this is just on creation date. And the rest of these bottom two queries have nothing to do with the column creation date. For the first two queries, it do have a lot to do with the column creation date. If we run these, we will see the first query.
Again, look at this ugly, awful, square bracket, dork formatting. And no as with the alias. Shame on you, SQL Server.
And the bottom query, of course, does. With the 1 equals select 1, this looks more like what I wrote. Right? We can see the literal for the date. We don’t have this thing get substituted with a parameter.
And so this is one of those things where 1 equals select 1 takes a little bit of the confusion out of either me zooming in, doing a video like this, presenting live, taking screenshots for a presentation. And when I zoom in and show the query text of a query, sometimes it’s kind of confusing when people don’t see the exact query that they just saw me run. And so a lot of times, just for clarity, it makes a lot more sense.
Even if I don’t include the 1 equals select 1 portion in the screenshot, it makes a lot more sense for me to take a screenshot of just this part so that you can see that it is actually the query that I was just talking about running with the literal values. If I told you I was running a query and then you saw this in the screenshot, you’d be like, where the hell did that come from? Is that in the store procedure now?
No, Eric, that looks nothing like the query you executed. Are you insane? Right? So there’s reasons, right? There’s reasons for these things.
Some of these reasons are presentation layer reasons. Others of them are truly query optimization reasons. And now, if we look at these two queries, now look, I agree that the second query is absolutely, absurdly ridiculous, right? There is absolutely no reason to ever use this index for the query that we’re running because it’s only on the creation date column.
But having this superfluous nonclustered index around actually makes, right? Because this is a valid plan choice, right? SQL Server would cost this choice and say, oh, maybe no.
But having that around is a reason why this top query, now to make things even kind of weirder. Here, this is where your noodle is really going to get baked because look what we have here, right? This looks like simple parameterization.
But over here, we have full optimization, right? So sometimes, even when you get full optimization for a query, sometimes you still need 1 equals select 1 to get rid of this ooky query text with the terrible dorky square brackets and the lack of an as in the alias. So 1 equals select 1 has some extra powers to it that even getting full optimization for a query doesn’t have for presentation stuff like this.
So that’s another good thing to keep in mind. Now, the other problem that you might run into with trivial plans, and this is something that I see a lot. So, like, you know, I think they used to be a lot more common.
I forget exactly. There were a few people who would always write articles comparing the query optimizer, the query optimizer’s abilities with, like, MySQL or Postgres and SQL Server or Oracle or DB2 or, like, you know, a whole bunch of different relational query engines. The problem is that, like, they may have had some specialty in, like, MySQL and or Postgres and or Oracle and or something else.
But they were pretty stupid about SQL Server. There were things that they didn’t know to look for and there were things that they just didn’t have the expertise in to, like, understand what, like, why things were different between certain engines. Now, granted, you probably shouldn’t need a very, very deep understanding to understand why SQL Server might look at a check constraint in one engine but then not do it in SQL Server.
But that was the case for a lot of things. And this is a pretty good example of that. So if I add this constraint to the users table, right, which just validates that every reputation in the users table is greater than or equal to one and less than or equal to two million because at this point in time, John Skeet still does not have two million reputations.
I forget what he’s up to. It’s been a while since I looked. Maybe I’ll check in after this video.
But then if I run these two queries and look at the execution plans, both of them return zero rows. And, of course, here’s where the demo gods have absolutely betrayed me because you know what I didn’t do? I didn’t drop this index on creation date.
So let’s remember to add that to the demo script next time. And let’s make sure that Erik Darling does 100 push-ups. Ah, my own petard.
There we go. That’s what I wanted. So this first query obviously scans the entire clustered index looking for where reputation equals this substituted parameter. Now, SQL Server needed a plan, right, since this is a trivial plan with simple parameterization.
SQL Server needed an execution plan that would be safe, that would be cacheably safe for any other execution of this query where it would maybe hit a rep. Maybe it would be looking for a reputation where, you know, what do you call it? Like it might exist in the table.
So, like, I’m searching for zero here, right? My search is for someone with a reputation of zero, and this query rightly does a constant scan because this query doesn’t get simple parameterization. This query doesn’t get a trivial plan.
And so SQL Server can logically detect that this query is not going to return any rows. We can just skip the whole thing. Again, with this query, of course, it can’t do that because of the parameter substitution over here. It has to say, well, if someone searches for reputation equals one or two or three or ten or five million next, we might need to actually look and see the return rows from the table.
We have to go figure that out. So if SQL Server wants to cache and reuse this plan, it can’t be the constant scan because the constant scan doesn’t touch the table, doesn’t return any rows, and blah, blah, blah, blah, blah, blah, blah, blah. Yeah. It’s a lot like how, well, I mean, not a lot like how, but it is reasonably close to sort of the neighborhood of why if you create a filtered index on, let’s say, creation date, and then like a stored procedure or in an entity, like an ORM query, you pass a parameter to search for creation date.
SQL Server can’t use that filtered index because, of course, you know, like it has to cache and reuse a parameterized plan where some parameter values might qualify to use a filtered index and some might not, right? So like it’s sort of similar to that where like the cached and reuse plan has to be safe for anyone, but the cached and reusable plan has to be safe for everyone. But, you know, a more specific, like, you know, more optimized for the literal value plan, like if you put option recompile or something on it, like that would be like a more, a plan that’s more specifically geared towards the query you’re running than a good, than a general plan that would work for any set of parameters.
So, I’m glad I got that off my chest, finally. It feels good. Feels real good. I feel like I stretched. I don’t know. I feel like I slept 18 hours. I’m just kidding. After my July 4th, it’s going to be a while before I feel like I slept 18 hours.
There was a lot of mezcal and brisket, which is a bit of an odd combo, but trust me on this one. They go well together. I am a fan. I am a newfound fan of mezcal and brisket in one mouth. All in the same mouth.
So, with that additionally off my chest, Eric’s cooking tips. Put mezcal and brisket in mouth. Mix, stir thoroughly.
Thank you for watching. I’m glad you made it to the end with me. I hope you enjoyed yourselves. I hope that you learned something. And of course, you know, as usual, if you like this sort of SQL Server content, please subscribe to my channel.
Join the nearly 3,824 other data darlings who get notified when I present these little bits of my love to you. When I show you my love.
If you like this video, comments, thumbs ups, things like that are nice. And as promised in my last video, I got a haircut. The whole thing.
Whole head. Granted, it’s looking a little dicey up there. I might do something about that. Because that’s a little bit much for a man of my incredibly young age. If I want to retain my beer gut magazine accolade of being the youngest and most handsome SQL Server consultant in the known universe, then I might want to put some serum on that.
Maybe grow a little bit of that hair back. Also, like, if I don’t do that, you know, maybe my hair guy will, you know, go out of business and starve and lose his house and his car.
That’d just be depressing. I got to keep getting haircuts to make the world keep going around. You know? That’s why we all do the things we do. Keep the ball spinning. So, anyway.
I’m going to go now. Thank you for watching. And I’ll see you in another video shortly. Thank you. Goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.