A Fine Slice Of SQL Server
Hasan Savran won’t settle for just one date:
GENERATE_SERIES function is introduced in SQL Server 2022 version. It is a simple function that generates a series of numbers within a given interval. It requires compatibility level 160.
The natural use for it is to generate a set of numbers, but as Hasan shows, you can easily turn that into a set of dates or dates and times.
Comments closedItzik Ben-Gan shows a good use of a pair of new T-SQL functions:
Comments closedTime-based grouping and aggregation are common in analyzing data using T-SQL—for example, grouping sales orders by year or by week and computing order counts per group. When you apply time-based grouping, you often group the data by expressions that manipulate date and time columns with functions such as YEAR, MONTH, and DATEPART. Such manipulation typically inhibits the optimizer’s ability to rely on index order. Before SQL Server 2022, there was a workaround that enabled relying on index order, but besides being quite ugly, it had its cost, and the tradeoff wasn’t always acceptable.
Daniel Hutmacher notes a performance killer:
In my last post, I found that DATEDIFF, DATEADD and the other date functions in SQL Server are not as datatype agnostic as the documentation would have you believe. Those functions would perform an implicit datatype conversion to either datetimeoffset or datetime (!), which would noticeably affect the CPU time of a query.
Well, today I was building a query on an indexed date range, and the execution plan contained a Merge Interval operator. Turns out, this operator brings a few unexpected surprises to your query performance. The good news is, it’s a relatively simple fix.
Click through for an example and some information on a fix. Hugo Kornelis also adds some good insights in the comments.
Comments closedDaniel Hutmacher noticed a problem:
As I was performance tuning a query, I found that a number of date calculation functions in SQL Server appear to be forcing a conversion of their date parameters to a specific datatype, adding computational work to a query that uses them. In programming terms, it seems that these functions do not have “overloads”, i.e. different code paths depending on the incoming datatype.
So let’s take a closer look at how this manifests itself.
Some of these results are pretty annoying, especially because I like to use DATETIME2(0) for the large majority of development work
Recently I had an interesting use case where I had to a compound calculation in Power BI. You can compound an interest rate for example, where you get a certain rate on your savings. Let’s say 1% (which is at the time of writing ridiculously high, but bear with me). After 1 year, you get 1% interest on your money. If you leave that (small) amount of money on your savings account, you’ll get 1% after another year on the original amount + the interest amount of the previous year. This means you’re money grows exponentially (sounds more exciting than it is in reality).
Read on for an example of creating what-if parameters around compound interest rates.
Comments closedDaniel Hutmacher does the math:
The modulus is the remainder of a division of two integers*. Suppose you divide 12 by 4, the result is 3. But divide 11 by 4, and the result is 2.75. This could also be expressed by saying that 11/4 is 2 with a remainder of 3. Computing that 3 is the work of the modulo operator, which in T-SQL is represented by the % operator.
Let’s explore how to compute the modulus of large numbers in SQL Server, and how this is useful in the real world.
Daniel’s example includes IBAN validation, though I think he’s secretly working on breaking asymmetric encryption…
Comments closedAaron Bertrand makes the case for calendar tables:
In a previous tip, Simplify Date Period Calculations in SQL Server, I described how to easily calculate certain dates, like the first day of a given month, quarter, or year. Another common scenario is trying to find the last weekday of the month, or the nth Monday or Wednesday. In this tip, I will show ways to simplify these calculations, both with and without a calendar table.
Click through for Aaron’s approach to the problem. I have a blunter approach in creating an expansive calendar table and using it. You do the heavy lifting one time and are good for life on that server.
Comments closedHasan Savran gives us some mixed news:
DateOnly and TimeOnly are the new members of .NET family, they are introduced in version 6. Developers have been asking to declare only a date or only a time without using the DateTime object for a long time. These two new functions will make the developer’s life easy for sure. You can declare them easily but saving them to a database can be a challenge. Before we go into those details for saving them, Let’s look at how to declare and use them in the code.
But right now, they aren’t so useful for SQL Server. Which is a shame, as there are obvious mapping candidates : DATE and TIME, respectively. My hope is that the Microsoft.Data.SqlClient library gets an update pretty soon to handle them. But read on to learn how Cosmos DB handles these new types.
Robert Cain doesn’t have time to wait:
What time is it? That’s what the KQL function
nowwill tell you. It will return the current date and time. It is mostly used in queries where you need data relative to the current date and time.
Read on to see how you can use it, including with offset intervals.
Comments closedI wrote about the Date_Bucket() function in SQL Server a couple weeks ago. Azure Cosmos DB team announced the same functionality with a different name DateTimeBin() function. It works exactly the same with the Date_Bucket() function of SQL Server.
Cosmos DB version of the function has the same number of parameters. The order is different. All the datatime parameters must be in ISO 8601 format (YYYY-MM-DDThh:mm:ss.fffffffZ)
Read on to see how it works.
Comments closed