Learn T-SQL With Erik: Date Bucket Difficulties
Chapters
- 00:00:00 – Introduction
- 00:00:33 – Date Bucketing Importance
- 00:02:21 – SQL Server 2022 Date Bucket Function
- 00:04:06 – Comparing Old and New Methods
- 00:05:39 – Date Bucket Function Details
- 00:08:45 – Sargability Considerations
Full Transcript
Erik Darling here, Darling Data, the best SQL Server consultancy with the most reasonable rates outside of New Zealand. Alright, in this video, we’re going to do some more T-SQL learning, and we’re going to talk about date bucketing. And why is this important? Well, if you work with dates in SQL Server, you might need to do stuff like this.
And if you are using a more modern version of SQL Server, like, say, SQL Server 2022+, you might want to take advantage of the new date bucket function in various ways. And we’ll talk about how we do that. Down in the video description, if you would like to purchase the larger corpus of the Learn T-SQL with Erik material, there is a coupon code down in the video description for $100 off the course price.
Thank you very much for watching, and please consider giving me a thumbs up if you enjoyed this video. And don’t forget to subscribe to my channel, where I’m always happy to answer any questions you may have. I’ll keep you updated on new videos, on other projects, on other projects that I come up with.
And with that, I hope you all have a great day. See you next time. Bye. of my reasonable rates and other areas you might also in your travels through the video description see all sorts of interesting links for free SQL Server performance monitoring which I offer free because I know you ever tried getting someone to pay for something it sucks yeah anyway it’s really good and it’ll help you find and fix your your SQL Server problems which from from what I can see in the world you you need help with so why not do it for free right why not why not do it for free and anyway let’s talk about the old date bucket yo rusty date bucket what am I clicking on down here all right this one okay this should all be fine so SQL Server 2022 introduced a function called date bucket where if we wanted to bucket time let’s say a six-hour increment okay so let’s say a six-hour increment and if we wanted to bucket time let’s say a six-hour increment and if we wanted to bucket time let’s say a six-hour increment I chose the month of October because October was one of my my favorite months and the whole calendar you would just simply have to do this date bucket our six and then whatever column you want to break down into buckets of six hours before the date bucket function date bucketing involved a lot of stuff like adding hours to the date diff between hours and something divided by six times six and I mean that just rounds out the date diff. Down here I have a couple other things that I’m doing like me getting all the hours in October with the generate series function and then me getting all the days in October with the generate series function here. But if we highlight this entire thing when we run it you’ll see that both of those things return equivalent results and it works out pretty well but one of them is much simpler than the other. So what we’re looking at here is well date buckets so we finally got to the first six hour bucket and then the 12 hour bucket and then the 18 hour bucket and then the 20 well the 24 hour bucket is down there. Well there’s there is no 20 I guess there is not a 24 hour bucket. 24 hour bucket people.
But it’s just a much simpler way of doing the same thing which is much harder and more mathematically annoying in older versions of SQL Server. As was doing this in older versions of SQL Server generate series truly makes life easier in many ways. But just like the date trunk function that we looked at date bucket returns a dynamic type whatever print whatever thing you pass in is what you get out. So you do have to be careful with you know various things that you’re going to get out of it. So if you’re going to get out of it you’re going to have to be careful with things that you might do with this in making sure that those things can compare well to you know columns in your database.
So like I was able to get a variety of different data types to return like date time to date time small date time date time and date time offset. All coming back from the date bucket function all via the magic of go away SQL prompt all the via the magic of SP describe first result set which tells you which gives you a which gives you a which gives you a description of your result set but only the first one. There’s no SP describe second result set or end result sets unfortunately. Date bucket is of course wonderful for grouping things like that. But you know just like any other function if you if you wrap it around a column you know all of a sudden that column sargability starts having some some wild difficulties. So we do have to consider that. So we’ve created an index on the the creation date column in the comments table right with our our favorite index creation options sort and temp DB and data compression if I were in a more highly concurrent environment I might consider other things like online equals on I might even consider if this was a really really big table I might even say max stop equals zero so that I can read from my source data structure with as many as many dops as I can. Right.
That’s that’s that’s a good idea. That’s how we live over here. We live efficiently.
Maybe not. I don’t know. I think we might also live deliciously sometimes. Depends on the day of the week. But, you know, just like what you would expect with any other column wrapped in a function, we get a scan of our index, which, you know, we would probably not be happy with if we cared a lot about performance.
But what’s also strange here, too, is that SQL Server estimates one row very reliably for a date bucket. So, you know, you might be careful about that as well, especially if you’re returning far more than one row. You might be unhappy with the one row estimate.
It might bring you back to the bad old days of table variables and off histogram values, stuff like that. But, yeah, anyway, I had a point with all that. Let’s do this.
So, usually, like any other date math thing, you really want to not wrap your column in the date bucket function. You really want to wrap whatever expression in the function and compare to that instead. Life is generally kinder to you, especially in SQL Server world, when you stop wrapping your columns in the…
These presentation layer functions and you start wrapping your expressions in presentation layer functions. Because that’s a scalar one-time thing, whereas when you run those functions against your columns, it’s every single row that has to pass through that function, which is far less kind to SQL Server.
Also, the storage engine cannot do anything with those functions. Those functions have to get passed up into the expression service. And the expression service is not merely an efficient mechanism for filtering, but it’s a storage engine.
It’s crazy once you start thinking about these things, isn’t it? It’s wild. You’ll see the same thing if you start working with variables in this whole mess.
But local variables, even wrapping local variables in expressions, will still get you the sort of, you know, same local variable weird cardinality estimates, the density vector…
Should we call it a guess? Should we call it an estimate? I don’t know. I don’t know. Call it whatever you feel like. Call it guesstimate. Land somewhere in the middle. Be neutral, you Swiss.
All right? But just like with any other situation, option recompile does tend to help things out a bit with local variables at the cost of…
The parameter embedding optimization is one of the primary benefits of option recompile. Maybe even the primary benefit of option recompile. Maybe even the primary benefit of option recompile.
Does get us back to a much more on-the-nose cardinality estimate. So, date bucket, date trunk, just like any other functions in SQL Server, don’t wrap them around your where or join clause columns, all right?
If you’re using local variables with them, beware, all right? It’s better to use literal values or option recompile. So, all that stuff applies as normal.
Now, one other stuff that’s worth talking about is… We have option recompile here. There’s not even a good reason for it because there’s no local variables or parameters that might be sensitive here, right?
But notice that SQL Server does not do a particularly good job of estimating how many rows might be compressed down once date bucketed, right? I’m pretty sure that…
Well, that’s a number right there, right? And that’s a number right there. And, well, I mean, that continues to be a number, but it’s a very wrong number, right? Not a very good estimate out of SQL Server on that one.
And that holds up, you know, pretty well across all the various cardinality estimator versions, right? We’re on this one. What does SQL Server guess?
Well, it’s a little bit better, right? We get at least somewhere near reality that we do. We do double jump. We do double aggregate that one.
It’s a little quirky. Anyway, this stuff isn’t that interesting. It all kind of stays the same. Anyway, date bucket, neither cardinality estimator reasons with it very well. You know, grouping by it, you might have a tough time in your query plans downstream with those cardinality estimates.
New or lost. Legacy cardinality estimator. The grouping estimates on that are kind of a mess. I mean, cardinality estimates for group by are never been great.
They’ve never been awesome. But with this function, this actually kind of reminds me, I want to go back and look at date trunk now. With this function in particular, it does a not great job.
So beware out there with your grouping by date buckets. You might have some problems that would require the services of a young, handsome consultant with reasonable rates to assist you.
All right. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where we will talk a little bit more about local variables and the presence of various date maths and filtering.
So I will see you for that joyous affair. All right. 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.