Is Distinct From

At first glance the Is Distinct From Operator seems similar to the Not Equal To (<>) Operator. But there is a small but certainly noteworthy difference.

You can check for yourself in a demo database with this one:

create table TestValues (
  ID int identity,
  Code nvarchar(3),
  Description nvarchar(50)
)
GO

insert into TestValues(Code, Description)
values
('A', 'Amsterdam'),
('B', 'Berlin'),
('C', 'Copenhagen'),
('D', 'Dubai'),
(null, 'Empty'),
('F', 'Fukuoka')
GO

select *
from TestValues
where Code <> 'A'

select *
from TestValues
where Code Is Distinct From 'A'
GO

drop table TestValues
GO

As you can see, the Not Equal To Operator does not show the record where Code has a Null value. This is some regularly overlooked behavior because it seems obvious that Null is not equal to the given Value. But since Null means literaly nothing, SQL Server will leave it out for the comparison.

If you want that record show up as well, you would need to use something like this:

select *
from TestValues
where isnull(Code, '') <> 'A'

With the Is Distinct From Operator, there is no need to do this. My guess is that the performance might also be better since using a Function on the left side of a Constraint can be bad. But I haven't tested this enough in larger databases to say that with certainty.

Anyhow, this is a nice addition and it is a good thing to understand the difference so you can determine which one you need.

First_Value and Last_Value

These new Window Functions are self-explanatory, but there is a catch when you use them. First_value gives you the lowest value for a specific Column in a given range. Last_value gives you, you guessed it, the highest value of that range.

That seems pretty easy, huh? But let's have a look at an example. First we need some data to do the test:

create Table Products (
ID int identity,
Code nvarchar(10),
Description nvarchar(50),
SpecialFinish nvarchar(20),
Price float
)
GO

insert into Products(Code, Description, SpecialFinish, Price)
values
('AB123', 'Superphone', 'Standard', 200),
('AB123RR', 'Superphone', 'Ruby Red', 210),
('AB123PP', 'Superphone', 'Pimple Purple', 225),
('AB123BB', 'Superphone', 'Bold Blue', 215),
('AB123GG', 'Superphone', 'Glossy Gold', 245)
GO

For this selection of Products I want to add a Column with the cheapest option, and one with the most expensive option. That can be done pretty simple using the First_Value and Last_Value Funcitons:

select Code, Description, SpecialFinish, Price,
first_value(SpecialFinish)
over (order by Price)
as Cheapest,
last_value(SpecialFinish)
over (order by Price)
as MostExpensive
from Products
Order by Code
GO

As you will see, the Cheapest option looks fine. The Standard finish is indeed the cheapest option for this Product and it is the same for every row. But what about the MostExpensive option?

Here it is different for every row, so definitely not one MostExpensive finish as you might expect! So what is going on here?

You might notice that for each row, the MostExpensive option is exactly the same as the SpecialFinish. And this explains why we get a different MostExpensive option for each row.

This has to do with the default behavior of the range from the Over part of the Window Function. By default it is looking at the range where you are at that moment. So for the first row the MostExpensive option is the same as the SpecialFinish. While handling the second row the MostExpensive option also the same as the SpecialFinish.

Why?

Because it does not take into account that there will be more rows following after that!

So to make good use of the First_Value and Last_Value function, you need to tell the range to be used should be from the first row, up until the last record found in the results. Here is the version that will give you the result as expected:

select Code, Description, SpecialFinish, Price,
first_value(SpecialFinish)
over (order by Price
rows between unbounded preceding and unbounded following)
as Cheapest,
last_value(SpecialFinish)
over (order by Price
rows between unbounded preceding and unbounded following)
as MostExpensive
from Products
Order by SpecialFinish
GO

So while it is easy to tell what these two new Window Functions do, the use of them is a bit more tricky than you might expect at first glance. And to find out why they might not behave as expected might take you some time to figure out.

But as you can see I already did that for you. Don't say I didn't warn you for this one. Now go and clean up the test Table.

drop table Products

LTrim(), RTrim() and Trim()

These Functions might seem familiar to you and that is because they are. LTrim and Rtrim have been around in SQL Server for as long as I can remember. And the Trim has been around since SQL Server 2017. So what's new here?

First of all, you can now tell the Trim Function if it should remove the leading or trailing spaces. Both leading and trailing spaces is also still possible and it even is the default behavior. So, with this feature added, the Trim Function can be used as a Ltrim or RTrim. But don't worry, those two still exist and can be used just as well. Removing them would break a lot of existing code and MIcrosoft will not remove them anytime soon.

Here are some examples of how the Trim Function is used and could be used to replace LTrim and RTrim:

select trim(' ABC123 ') as FullTrim1
select trim(both ' ' from ' ABC123 ') as FullTrim2
select trim(leading ' ' from ' ABC123 ') as LeftTrim
select trim(trailing ' ' from ' ABC123 ') as RightTrim

Now take a good look at the Trim Functions where it is specified which part of the String should be trimmed. As you can see I specified that a Space should be trimmed, which is the default behavior if you don't tell anything to the Trim Function.

Does that suggest we could trim other characters as well from a String? Let's find out!

select trim('###ABC123###') as FullTrim1
select trim(both '#' from '###ABC123###') as FullTrim2
select trim(leading '#' from '###ABC123###') as LeftTrim
select trim(trailing '#' from '###ABC123###') as RightTrim

Hey! That just works! That's kinda cool. But what if we need to trim multiple characters from the String? Can that be done as well?

Let's find out!

select trim(both '|# ' from '# #|#ABC123###')

Great! That also works. It's almost like a replace. Almost, but it is still a Trim Function. See for yourself with this one:

select trim(both '|# ' from '# #|#ABC#123###')

Once a character is found that should not be trimmed, the trimming stops from the direction it started. In this case the trimming from the left stopped at A. From the right side the trimming stopped at 3. That is why the # in the middle is still there.

So, while these Functions aren't new in SQL Server 2022, the improvements are surely pretty useful.

That's it for today!

And that's it for the new and improved Functions for SQL Server 2022. Be sure to check out Part 1 on this topic. And also check out the new Date_Bucket Function as well. Lot's of shiny new things and improved Functions you should get into your skillset.

Did I miss anything? Please let me know by leaving a comment. Use the comment also to let me know what your favorite is!

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!