What is new in the CTP 2.1 of SQL Server 2022? New T-SQL commands!

Hi guys,

It's time for news,  the new CTP 2.1 of SQL Server 2022 has just been released.

Let's see together what are the innovations introduced.

Enjoy!

 

 

SQL Server 2022 CTP 2.1

 

I love talking about T-SQL commands and so I'm happy to show you the new T-SQL commands just added with this latest CTP (community technical preview) 

Bit Manipulation functions

Bit manipulation functions are introduced perhaps to match other database engines such as mysql.

They could be useful in certain areas

 

Now we have a LEFT_SHIFT function.

 

Through this function we can shift the bits of an integer to the left.

There are 2 parameters. the input decimal value and the number of bit to shift.

 

For example:

select LEFT_SHIFT(256,1)
    

Heres the result:

Rememeber that shifting one bit to the left is equal to multiply the initial value by 2.

The RIGHT_SHIFT function instead shift bits to the right. 

Shifting to the right is equivalent to divide by 2.

 

For example:

select RIGHT_SHIFT(256,1)
    

Heres the result:

 

These two functions are not sargable.

Other functions have been introduced.

BIT_COUNT() function returns the number of bits needed to rapresent the binary input value.

select BIT_COUNT(101)
    

This select return a value of 4.

 

 

GET_BIT() function returns the value of the j-th bit.

The function accepts in input two parameters: the inputer binary value and the j-th bit 

 

For example the following select

select GET_BIT(0010, 2)
    

Will return the value 1 because the value of the second bit from the right is 1.

SET_BIT function sets the j-th bit from the right to one or to zero.

 

For example.

 

With the following command, we can set the third bit from the right to 1

select SET_BIT(10000, 3, 1)
    

we get the value 10008

Datetrunc

And now we can talk about another feature!

If you remember from previous posts, the first CTP 2.0 contained some news on the treatment of dates

This CTP 2.1 adds the function DATETRUNC.

 

This function rounds the date passed to the function by the first parameter.

 

For example:

select DATETRUNC(month, cast('2017-09-02 00:00:00.000' as datetime))
    

You will get the date "2017-02-01 00:00:00.000" rounded off per month.

This function is unfortunately not sargable.

 

Distinct predicate

 

 

Again, you can now use the distinct predicate using this syntax (also used by other rdbms)

select * from TabVal where val IS NOT DISTINCT from tabval.id
    

This way you can compare the equality of two expressions and guarantee a true or false result even if one or both operarands are NULL.

The statistical side of sql server

 

Finally just a little bit of statistics with these new features:

 

We have these two new functions: APPROX_PERCENTILE_DISC() and APPROX_PERCENTILE_CONT()

The first function returns the value from the set of values in a group based on the provided percentile and sort specification and it is an alternative to PERCENTILE_DISC for large datasets.

 

For example:

select APPROX_PERCENTILE_DISC(1) WITHIN GROUP(ORDER BY val) from TabVal
    

The other function returns an approximate interpolated value from the set of values in a group based on percentile value and sort specification.

 

For example:

select APPROX_PERCENTILE_CONT(1) WITHIN GROUP(ORDER BY val) from TabVal
    

 

 

That's all for today guys! 

I hope you enjoyed this post and you have find it useful.

 

If you find an advertisement that genuinely interests you simply click it from on my blog, please. You will help me keep writing more and more interesting content. ~Luke

 

 

 

 

 

  

 

 

 

 

 

 

 

 

 

 

Previous post: A book a day! Review of the book "SQL Server Query Tuning and Optimization" by Benjamin Nevarez.