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:

select
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:

select format(getdate(), 'dd-MM-yyyy')

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:

with CustomerRow as (
  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:

with CustomerRow as (
  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:

select greatest(1, 3, 5, 2, 4, 6)

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:

select value from generate_series(1, 100)

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:

select value from generate_series(1, 100, 3)

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:

select dateadd(day, value, '2024-01-01') as TheDate
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:

select CustomerID,
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:

select CustomerID,
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:

select
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:

declare @Unassigned varchar(max) = '10710,10711, 10712'
select * from string_split(@Unassigned, ',')

Now, with SQL Server 2022, we can also do this:

declare @Unassigned varchar(max) = '10710,10711, 10712'
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:

declare @Unassigned varchar(max) = '10710,10711, 10712'
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:

create table SalesPersonLeads (
SalesPersonID int identity,
Name nvarchar(50),
CurrentLeads int )

Now let's insert some values for testing:

insert into SalesPersonLeads(Name, CurrentLeads)
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:

declare @Unassigned varchar(max) = '10710,10711, 10712'

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!