562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
|New SQL Language Features for SQL Server 2022|
|SQL Server 2022 is now in preview (you can download an evaluation copy). It includes new functions to split strings, trim text and truncate dates, among other things.|
You can now download an evaluation copy of SQL Server 2022 (internal version 16.x) - so I did!
You can see all of the changes for the new version - and also a download link for an evaluation copy - at this Microsoft site.
The interesting new language features are as follows:
|Splitting strings||The website announces the STRING_SPLIT function, which allows you to turn a comma-delimited string of values into a table. I got really excited about this, but then realised I'd written a full blog announcing this back in March 2021!|
|Trimming text||You can now specify which characters you want to trim from the front or back of a string of text (see below for more on this).|
|Truncating dates||You can now choose to what extent you want to simplify a date or date/time (for example, you could just pick out the year, or the year/month). The only way to understand this is with examples, which I've included below.|
|JSON functions||There are new functions to check if a string of text is in JSON format and to generate JSON objects, but with so many better tools around for importing and exporting JSON it's hard to see you doing this in SQL Server Management Studio.|
|Time series functions||You can now generate a series of numbers and also pick out the last day, week, month, etc in a time interval, but again it's hard to see why you would ever choose to work with time series data in SQL!|
The updated LTRIM, RTRIM and TRIM functions allow you to specify which characters you want to remove from the beginning and end of a string of text. For example, consider this query:
trim(BOTH 'e ht' FROM f.FilmName) AS TrimmedTitle
tblFilm AS f
f.FilmName like 'the%'
This might give these results for a table of films:
We've tried to remove the The from the start of each film (it didn't matter which order we put the characters in, as they'll each be removed separately), but this has played havoc with Terminator!
Obviously in the above example we could have avoided lopping characters off the end of film names by using LTRIM, or solved the problem more reliably using the replace function.
The DATETRUNC function is similar to the EOMONTH function in Excel, but has two differences: it picks out the start of a period and not the end of it, and you can use it for any time period (from microseconds all the way up to years).
Here's an example of the use of DATETRUNC to show film release dates:
-- date format used
DECLARE @df varchar(max) = 'ddd dd MMM yyyy'
-- the release date of this film
FORMAT(f.FilmReleaseDate,@df) AS ReleaseDate,
-- truncating the month
FORMAT(DATETRUNC(month,f.FilmReleaseDate),@df) AS FirstDayOfMonth,
-- truncating the quarter
FORMAT(DATETRUNC(quarter,f.FilmReleaseDate),@df) AS FirstDayOfQuarter,
-- truncating the year
FORMAT(DATETRUNC(year,f.FilmReleaseDate),@df) AS FirstDayOfYear
tblFilm AS f
Here's the start of the output this might give:
For each film you see its release date, the start of this month, the start of this quarter and the start of this year.
This is probably the update's most useful function (you can subtract one day from the start of a period to get the end of the preceding one), but it has a strange name! Why not call it StartOfPeriod, or something like that?
25 Aytoun Street