New in SQL 2022 (1)
With SQL Server 2022 available since May 2023, it is time to dive into what is new.
With every update, the SQL Server engine itself gets improved.
But besides that, there are also some new and useful functions.
In this post we will have a look at what's new in SQL Server 2022.
And we will check some examples of why and when these new features are useful.
First a bit of background
Some time ago in 2017 I was asked to help one of our consultants with a Query. I noticed something I didn't recognize, although I understood what it was supposed to do. It had to do with formating a Date like dd-MM-yyyy. Up until then I was used to use this one:
right('00' + cast(datepart(day, getdate()) as nvarchar(2)), 2)
+ '-' +
right('00' + cast(datepart(month, getdate()) as nvarchar(2)), 2)
+ '-' +
cast(datepart(year, getdate()) as nvarchar(4))
But what I saw was this one:
Much, much easier, huh?
And to my surprise it worked like a charm!
At that point I realized I was using that complex verbose version since SQL Server 2000.
And since that worked fine, I never bothered to check whether it could be done much simpler.
I could have said that I didn't use it because of backwards compatibility, but since it had been around for about 10 years that would not hold.
Still it is one thing to consider.
This post is about what is new in SQL server 2022 which is out and about for less than a year now.
Using these functions in something you are going to distribute to multiple custommers is not the best idea.
Not everyone will already be using this version and these functions will not be recognized in older versions.
But if you are sure your customer is using this version, be sure to check them out.
If not for any case right now, you will use them more and more in the future.
Greatest and Least
This is a nice one which you can compare with the Min and Max function.
Min and Max can find the lowest or highest value within a column, across the Rows.
Greatest and Least can find the lowest or highest within different columns in a single Row.
Let's say you have a Row with a Customer's default discount and a special discount.
If the special discount is more that the default discount, the special discount should be applied.
If not, the default discount should be used.
For that you had to use the Case Statement like this:
select newid() as CustomerID,
10 as DefaultDiscount,
15 as SpecialDiscount
)
select CustomerID,
case
when SpecialDiscount > DefaultDiscount then SpecialDiscount
else DefaultDiscount
end as AppliedDiscount
from CustomerRow
You can still do this in SQL Server 2022.
But when you need to check more than 2 Columns, the Case Statement can become less readable.
So, in SQL Server 2022 you can do it like this:
select newid() as CustomerID,
10 as DefaultDiscount,
15 as SpecialDiscount
)
select CustomerID,
greatest(DefaultDiscount, SpecialDiscount) as AppliedDiscount
from CustomerRow
Now with just 2 options, it is not that much easier. But it is easier to read, and certainly when you have more options to evaluate. Just expand the Variables you use for the Function:
From the new functions this one seems to be the most useful to me for now.
Generate Series
To generate a series of numbers or dates, you could (and still can) use a Recursive Common Table Experssions.
But there is a way much easier option in SQL Server 2022.
You can now use this one to generate a series of numbers from 1 to 100, using an interval of 3:
This will give you a result set with all the numbers from 1 to 100. Now if you would rather use an increment of 3 to get 1, 4, 7, 10 et cetera) you have an extra parameter for that:
Generate Series always returns one Column which is always named Value. Generate_series can only generate a series of numbers or floats. But with a bit of creativity you can also use it to generate a series of dates:
from generate_series(0, datediff(day, '2024-01-01', '2024-12-31'))
Window Clause
Window Functions are great to get running totals, row numbers and other useful numbers. If you are familiar with Window Functions, you will easily understand this one:
datepart(year, InvoiceDate) as Year,
datepart(Month, InvoiceDate) as Month,
count(*) over (
Partition by CustomerID,
datepart(year, InvoiceDate),
datepart(Month, InvoiceDate)
) as Quantity,
sum(Amount) over (
Partition by CustomerID,
datepart(year, InvoiceDate),
datepart(Month, InvoiceDate)
) as Amount
from Invoiceheaders
As you can see, the Window for the Quantity and Amount are both the same.
Yet, pre SQL Server 2022 they needed to be specified twice.
Obviously, this could lead to mistakes if things need to change in the future.
One Window is edited, the other one might be overlooked.
As of SQL Server 2022, we can now specify a Window Clause, give it a name and use that name where needed:
datepart(year, InvoiceDate) as Year,
datepart(Month, InvoiceDate) as Month,
count(*) over Separation as Quantity,
sum(Amount) over Separation as Amount
from Invoiceheaders
window Separation as (
Partition by CustomerID,
datepart(year, InvoiceDate),
datepart(Month, InvoiceDate)
)
Since the Window Clause expands the options of a Query, it needs to be in its designated place. Here is the order you should use when writing a Query:
from
where
group by
having
window
order by
String_Split
String_split already existed in SQL Server 2016, but it has gotten an additional parameter and an additional value. Looking back at SQL Server 2016, we could already do this:
select * from string_split(@Unassigned, ',')
Now, with SQL Server 2022, we can also do this:
select * from string_split(@Unassigned, ',', 1)
Notice the third parameter.
If you leave this one out, or set it to 0 (zero) it works just as before.
But if you set it to 1, it this will affect the result set.
Besides the Value, you will also get the Ordinal representing its position in the given string.
Now if we want the second value from the string, we could do this using the Ordinal:
select * from string_split(@Unassigned, ',', 1)
where Ordinal = 2
To give you an example of what you can do with this, let's set up a case.
Let's say we have a bunch of Sales People, each with a number of Leads.
We have some new Leads and we want to assign them to the persons with the lowest number of Leads.
For this we will need a Table with the Sales Persons and their current number of Leads.
We can create that Table like this:
SalesPersonID int identity,
Name nvarchar(50),
CurrentLeads int )
Now let's insert some values for testing:
values('Bob Slayer', 27),('Brad Wurst', 24),
('Joe King', 28),('Annie Key', 26),('Tim Burr', 21),
('Rita Lynn', 19),('Stu Pitt', 18),('Ted Pole', 23)
select *
from SalesPersonLeads
Now we can use string_split to create a table with the 3 new Leads.
And we can join that Table with the SalesPersonLeads Table.
All we need now is to sort them by the number of current Leads.
And to join them, we need to have that row number, which we can do with a Window Function.
We will use that row number to join on the ordinal of the string_split Table.
That would give us something like this:
select Lowest.SalesPersonID, Lowest.Name,
Lowest.CurrentLeads, Assignees.value as NewCustomer
from string_split(@Unassigned, ',', 1) as Assignees
inner join
(
select top 3 SalesPersonID, Name, CurrentLeads,
row_number() over (order by CurrentLeads asc) as Position
from SalesPersonLeads
order by CurrentLeads asc
) as Lowest
on Assignees.ordinal = Lowest.Position
As you can see, the addition of the Ordinal position makes the string_split Function much more useful. Since string_split returns a Table, we can also use it to Join the result to other Tables of Views.
That's it for today!
Wait! Ins't there more in SQL Server 2022?
Yes, there is. But I will leave that for another post.
To be honest, I have not found any good practical examples on how to use these other Functions.
So, until I do, enjoy and play around with these ones.
As I said, do be carefull because they are not supoorted on lower versions of SQL Server.
So be sure to check whether you can use them in your case.
Even though there are many improvements in SQL Server 2022, your customer might not be willing to upgrade.
You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!