T-SQL Tuesday 159 - What’s Your Favorite New Feature? - Magda
This month the #TSQL2SDAY invitation comes from Deepthi Goguri (blog|twitter) who asks us to write about our favorite new feature in SQL Server 2022 or in Azure and New Year’s Resolutions. The T-SQL Tuesday is a monthly blogging event that was created by Adam Machanic (blog) and is maintained by Steve Jones (blog|twitter).
If you want to find out about my New Year’s Resolutions read this post.
There are many new features in SQL Server 2022, I’ve done my research, and I found quite several updates and new features that really interest me, and there were mostly features about the Language, the new/updated T-SQL functions such as:
From MS Learn:
DATETRUNC() function returns an input date truncated to a specified datepart.
On the surface the work similarly to
DATEPART(), however that function returns integer values, opposed to the dates returned by
DATETRUNC() (we will see that better in the example below).
Let’s consider a query:
declare @d datetime2 = '2023-02-14 23:02:14.2302149'; select '10 - Year' part, DATETRUNC(year, @d) dateTRUNC , DATEPART(year, @d) datePART union select '11 - Quarter', DATETRUNC(quarter, @d), DATEPART(quarter, @d) union select '12 - Month', DATETRUNC(month, @d), DATEPART(month, @d) union select '13 - Week', DATETRUNC(week, @d), DATEPART(week, @d) -- For a U.S. English environment, @@DATEFIRST defaults to 7 (Sunday). union select '14 - Iso_week', DATETRUNC(iso_week, @d), DATEPART(iso_week, @d) union select '15 - DayOfYear', DATETRUNC(dayofyear, @d), DATEPART(dayofyear, @d) union select '16 - Day', DATETRUNC(day, @d), DATEPART(day, @d) union select '17 - Hour', DATETRUNC(hour, @d), DATEPART(hour, @d) union select '18 - Minute', DATETRUNC(minute, @d), DATEPART(minute, @d) union select '19 - Second', DATETRUNC(second, @d), DATEPART(second, @d) union select '20 - Millisecond', DATETRUNC(millisecond, @d), DATEPART(millisecond, @d) union select '21 - Microsecond', DATETRUNC(microsecond, @d), DATEPART(microsecond, @d);
And the results are as below:
part dateTRUNC datePART ---- --------- -------- 10 - Year 2023-01-01 00:00:00.0000000 2023 11 - Quarter 2023-01-01 00:00:00.0000000 1 12 - Month 2023-02-01 00:00:00.0000000 2 13 - Week 2023-02-12 00:00:00.0000000 7 14 - Iso_week 2023-02-13 00:00:00.0000000 7 15 - DayOfYear 2023-02-14 00:00:00.0000000 45 16 - Day 2023-02-14 00:00:00.0000000 14 17 - Hour 2023-02-14 23:00:00.0000000 23 18 - Minute 2023-02-14 23:02:00.0000000 2 19 - Second 2023-02-14 23:02:14.0000000 14 20 - Millisecond 2023-02-14 23:02:14.2300000 230 21 - Microsecond 2023-02-14 23:02:14.2302140 230214
So clearly we see the difference between the values returned by the two functions.
One thing to note is that for
Iso_week the first day of the week in the ISO8601 calendar system is Monday, while
Week uses by default Sunday For a U.S. English environment.
LEAST() & GREATEST()
From MS Learn:
GREATEST()functions return the minimum / maximum value from a list of one or more expressions.
If the data type is various then in the return I will receive the data type of the highest value before comparison. It is similar to
MIN() functions, however
GREATEST() accept more arguments (up to 254) and they work differently on columns.
Let’s consider this example with multiple arguments:
create table tsql2sday (s1 int, s2 int, s3 int); insert into tsql2sday values (2,1,3),(5,4,3),(4,2,3),(40.1,110.12,398.1); select *, least(s1,s2,s3) least, greatest(s1,s2,s3) greatest from tsql2sday;
The results show that
GREATEST() work horizontally:
s1 s2 s3 least greatest -- -- -- ----- -------- 2 1 3 1 3 5 4 3 3 5 4 2 3 2 4 40 110 398 40 398
From MS Learn:
A table-valued function that splits a string into rows of substrings, based on a specified separator character.
The STRING_SPLIT() is not new, but has been updated in SQL Server 2022 with
enable_ordinal argument and
ordinal output column. Again from the MS Learn:
enable_ordinalserves as a flag to enable or disable the
ordinaloutput column. A value of 1 enables the
NULL, or has a value of 0, the
ordinalcolumn is disabled.
Let’s have a look at this query:
SELECT * FROM STRING_SPLIT('This month the #TSQL2SDAY invitation comes from Deepthi Goguri',' ');
and the results would be in a single column,
value ----- This month the #TSQL2SDAY invitation comes from Deepthi Goguri
but starting from SQL Server 2022 we can add this extra argument to enable the
ordinal column in the output:
select * from STRING_SPLIT('This month the #TSQL2SDAY invitation comes from Deepthi Goguri',' ', 1);
and now we got more information:
value ordinal ----- ------- This 1 month 2 the 3 #TSQL2SDAY 4 invitation 5 comes 6 from 7 Deepthi 8 Goguri 9