Better Filtering With YEAR and MONTH Functions In SQL Server

Better Filtering With YEAR and MONTH Functions In SQL Server

---

This week, I’ve got a coupon that offers an even steeper discount on my training than the one at the bottom of the post.

It brings the total cost on 24 hours of SQL Server performance tuning content down to just about $100 USD even. Good luck finding that price on those other Black Friday sales.

It’s good from December 23rd-27th, or until I remember to turn it off. Hurry along now.

Thanks for watching!

Video Summary

In this video, I delve into the world of better filtering techniques in SQL Server queries, specifically focusing on how to handle year and month functions more effectively. Often, I encounter queries that dissect dates into their constituent parts (year and month) for comparison, which can lead to performance issues and slower execution times. By demonstrating a cleaner approach using the `DATEFROMPARTS` function, I show how to avoid these problems and improve query sargability. This method not only makes your code more readable but also ensures that SQL Server can leverage existing indexes efficiently, leading to faster execution.

Full Transcript

Erik Darling here. Guess who? Darling data. That’s what science says. In today’s video, we’re going to talk about better filtering with year and month functions. Now, I run into a lot of queries that do something like take a year and a month, very rarely a day, but usually year and month, is either parameters or parameters or sometimes they even go so far as like to dissect a date and pull the year and month out. I don’t know why. It’s blasphemous, but they do it. And then they take some columns in their table and they like a date column or a date time column and they say where year, month, that column equals the month thing and then where year column equals the year thing. And without fail, those queries suck and are slow and have problems and I have to fix them. So I’m hoping that by recording this video, I can avoid having to fix those problems in the future. Great. How much would you pay for that? How about four bucks a month? You click the link in the video description. You can sign up for a membership and get all of this for four bucks a month. You can sign up for a membership and get all of this for four bucks a month. You can sign up for a membership.

a month or more if you choose to be more generous, but four bucks a month is the minimum. Anything less than that, I don’t know. What do you do with it?

I live in New York. Four bucks technically just allows you to take a breath. If you are just a fan of this content, you can like, you can comment, you can subscribe.

I’ll thank you in some other way. If you are looking for help with your SQL Server, perhaps with some performance tuning issues like with wrapping year and month in columns, I am best in the world at all of these things.

You can hire me instead of some idiot who doesn’t know what to do and who gives you bad advice. So that’ll be cool, right? Imagine that you’ll actually get stuff done. If you would like some very high quality, very low cost SQL Server training that teaches you things like I’m about to teach you now and more.

You can get all of my training for about 150 USD with that discount code. And would you believe that a link directly to go to the training site and apply that very discount code is in the video description.

So, again, it’s the end of the year. I’m not doing anything. Talk to me in 2025. With all that out of the way, let us put on our party hats and commence the partying because that is what we are here to do.

So, this is the type of query that I see people writing quite a bit. Oh, is ZoomIt? No, ZoomIt is just a little bit delayed. This is what I see people doing quite a bit.

Now, I think that part of why they don’t do things correctly is because sometimes figuring out date things is hard. And I admit I also didn’t want to figure out date things.

So, what I did is if ZoomIt will finally just go away, is I looked at Stack Overflow. And Stack Overflow had a pretty good answer for how to figure out if you’re in a leap year. So, here’s what I’m doing.

I have an index on the votes table on creation date. I just didn’t want that to accidentally run again. And then up here, I’m assigning a year and a month. Let’s just pretend these could be parameters.

They could be things that you dissect from a date that gets passed in. Whatever you want to do with it. It doesn’t matter to me. And then in here, I’m figuring out if we are in a leap year. So, if we’re in February, then we start with, to find the last day of the month, we start with 28.

And then if these things are true, we add 1 to the 28 or else we add 0 to the 28, which makes sense. Because you don’t want to add anything if it’s not in a leap year. And then there’s a little bit of figuring out down here to say, if your month is 4, 6, 9 or 11, then you have 30 days in your month.

If you are not month 4, 6, 9 or 11, then you have 31 days. All right. So, we’ve got that part figured out.

Great. Great. Wonderful. So, this is really a much better, oh, why are you moved over? How did that happen?

Oh, that just looks silly, unprofessional, didn’t it? That was terrible. Just fire whoever wrote these scripts. Really, a much better way, a much cleaner way of doing this. And again, this comes down to the concept of sargability, of being able to take whatever your search arguments are and apply them easily to whatever index you have in place.

Of course, for us, we have an index on the creation date column. If we do stuff like this, SQL Server has to run this function for every column, figure out, pull out the month, pull out the year, and figure out if that month and year is equal to the month and year that we have passed into the query. If we do this instead, using the wonderful, magnificent date from parts feature, we can assemble a date out of the year and the month.

And to find the start of the month, we just put in 01. And to find the end of the month, we just use that day variable that we used up there. Now, because I am using local variables, and local variables have lots of performance side effects, particularly usually because of poor cardinality estimation, in order to shortcut that issue, in order to force the parameter embedding option that SQL Server has in its wonderful engine, I am using the option recompile hint here.

If these are big queries that run occasionally, and you are okay with them recompiling every time, please use a recompile hint. There is nothing wrong with it. If this is code that you want to be production ready, awesome code, you may want to explore two other scenarios. One might be to have a store procedure that accepts the local variables that you declare and assign values to as parameters, because then they are treated like parameters, because they are passed in to that store procedure.

Or, you could use dynamic SQL and parameterize dynamic SQL, which is the only safe kind of dynamic SQL, and you could pass in the local variables that you declare and set values to as parameters to that dynamic SQL, thereby transmutating them through the alchemy of parameterization into parameters. Wonderful. Things that you would just never think would work, but they work.

They work well. And of course, if we run these two queries, I am just going to hit execute on this. These both come back relatively quickly, but the first one comes back relatively slower.

This one takes a full second to run, has to scan the entire index that we created on the votes table. That is this thing right here. And if we hover over the operator here and we look at the tooltip, we actually have something kind of funny happen.

Year and month are really just sort of shortcut synonyms for the date part function. Right? So, you can see that we have evaluated…

Oh, gosh. Where did my cursor go? There it is. The date part month right here and the date part year right here. And we have said where the date part month equals three, just like we assigned to that value.

And the creation date and the year 2013. Oh, that’s nice. We still had to scan that index though.

We needed a parallel query for it to be fast. If we wanted to put these on sort of even footing, and I’ll do that in a second, we could run this query at max stop one to see how long this takes. Because down here, we do have a serial query that finishes in 178 milliseconds.

Right? And granted, the plan is a little bit more complex with all this stuff in it. But in this case, it’s okay because each one of these constant scans is really just creating a row for the date from parts function that we’re feeding in here.

And if we look at this, we just have a range seek to for expression 1008, which is the date from parts function that we created from year month 01. And then down in here, we have the end of that for expression 1009, which is the date from parts function that we assembled for year month day. Right?

So when we found the end day of the month, that’s where that was. So let’s actually go back and let’s redo that. Let’s say that we want to put these on equal footing. Right?

We want to make sure there’s no cheating. Parallelism clearly made things faster and better for that first query. Right? So let’s look at just how long, like single threaded each of these takes. Right?

Let’s look at these execution plans. This takes a full five and a half seconds now. So without the benefit of parallelism, this takes 5.6 seconds. Right?

We still have to scan that entire index. It’s still doing the same thing. And this query down here is still able to, in just under 200 milliseconds, do that same work. So if you’re out there tuning queries, and you see something like this, well, stop doing it. One.

The first step, stop. Second step, start. This is a much better way of approaching this. Now, this sort of thing won’t work if you have to do something real goofy, like the year and month from the date value in one column equals the year and month from a date value in another column. Maybe I’ll do a video about that someday, but no promises right now because my time is dedicated to other things.

You might notice that I have about 11 tabs open up there. I’m going to be recording some videos about stuff that I find interesting, about how to write query, how to write T-SQL queries correctly, how to use different things in T-SQL to write queries in the best way possible. So I’m going to be recording those.

Maybe I’ll get around to that one as soon as I can, but I got a lot of stuff to talk about before I get around to that. So anyway, thank you for watching. I hope you enjoyed yourselves.

I hope you learned something and I hope that this finds you in good health because nice, happy, healthy people, they want to buy training and spend time with young, handsome consultants and gosh darn it, that’s me. All right. Cool.

Thank you for watching.

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.