{"id":94710,"date":"2022-07-04T17:00:53","date_gmt":"2022-07-04T17:00:53","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=94710"},"modified":"2022-07-01T22:20:18","modified_gmt":"2022-07-01T22:20:18","slug":"whats-new-in-t-sql-in-sql-server-2022","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/whats-new-in-t-sql-in-sql-server-2022\/","title":{"rendered":"What’s new in T-SQL in SQL Server 2022"},"content":{"rendered":"

There are many new features in SQL Server 2022<\/strong>. In relation to T-SQL<\/strong> querying, there are a few as well and they usually are left for last in face of many other new optimization features.<\/p>\n

Sample scenario<\/h2>\n

These samples are built on the AdventureWorksDW2019 database installed in a SQL Server 2022 CTP 2.<\/p>\n

Date_Bucket<\/h2>\n

Let’s first consider a sample query. The following query shows information about internet sales and products.<\/p>\n

SELECT<\/span>\u00a0p<\/span>.<\/span>productkey<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0englishproductname<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0orderdate<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0salesamount<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0dimproduct<\/span>\u00a0p<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER<\/span>\u00a0JOIN<\/span>\u00a0factinternetsales<\/span>\u00a0fi<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON<\/span>\u00a0p<\/span>.<\/span>productkey<\/span>\u00a0=<\/span>\u00a0fi<\/span>.<\/span>productkey<\/span>\u00a0 <\/span><\/div>\n

\"\"<\/p>\n

 <\/p>\n

The Date_Bucket<\/strong> function, as the name implies, calculates date buckets of one specific size. Given a date and the bucket size, the function will return the start date of the bucket containing the date.<\/p>\n

This is very useful to classify the facts in our data in groups according a date bucket. For example, we can create 2 weeks bucket, 2 months bucket and so on. The date bucket function is useful for grouping on these scenarios.<\/p>\n

For example, based on the query above, let’s create a 1 week date bucket to group the product sales.<\/p>\n

SELECT<\/span>\u00a0p<\/span>.<\/span>productkey<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0englishproductname<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Date_bucket<\/span>(<\/span>week<\/span>,<\/span>\u00a01<\/span>,<\/span>\u00a0orderdate<\/span>)<\/span>\u00a0week<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Sum<\/span>(<\/span>salesamount<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0SalesTotal<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0dimproduct<\/span>\u00a0p<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER<\/span>\u00a0JOIN<\/span>\u00a0factinternetsales<\/span>\u00a0fi<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON<\/span>\u00a0p<\/span>.<\/span>productkey<\/span>\u00a0=<\/span>\u00a0fi<\/span>.<\/span>productkey<\/span>
\nGROUP<\/span>\u00a0\u00a0BY<\/span>\u00a0p<\/span>.<\/span>productkey<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0englishproductname<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Date_bucket<\/span>(<\/span>week<\/span>,<\/span>\u00a01<\/span>,<\/span>\u00a0orderdate<\/span>)<\/span>
\nORDER<\/span>\u00a0\u00a0BY<\/span>\u00a0productkey<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0week<\/span>\u00a0 <\/span><\/div>\n

\"\"<\/p>\n

If we change the size of the bucket to 2 weeks, instead of one, you may notice on the following image the dates organized for each two weeks.<\/p>\n

The calculation of the buckets needs a starting point. This is an optional parameter. When we don’t specify the starting point, the calculation starts on 01\/01\/1900. That’s how it was calculates on the previous two queries.<\/p>\n

There is no surprise the Date_Bucket<\/strong> expression is not a SARG<\/strong>. As you may notice on the execution plan below, the index operations are all SCAN<\/strong>.<\/p>\n

\"\"<\/p>\n

 <\/p>\n

The query plan complains about some missing indexes. Let’s create them first and analyse the impact of Date_bucket<\/strong> isolated from other needs in the query.<\/p>\n

CREATE<\/span>\u00a0INDEX<\/span>\u00a0indprodkey<\/span>
\n\u00a0\u00a0ON<\/span>\u00a0dimproduct<\/span>(<\/span>productkey<\/span>)<\/span> <\/p>\n

CREATE<\/span>\u00a0INDEX<\/span>\u00a0indfactprodkey<\/span>
\n\u00a0\u00a0ON<\/span>\u00a0factinternetsales<\/span>(<\/span>productkey<\/span>)<\/span>
\n\u00a0\u00a0include<\/span>\u00a0(<\/span>orderdate<\/span>,<\/span>\u00a0salesamount<\/span>)<\/span>\u00a0 <\/span><\/div>\n

After these indexes are created, the query plan will be like this one below:<\/p>\n

\"\"<\/p>\n

 <\/p>\n

Let’s execute the follow statements followed by the query execution to get a clean statistics. Don’t do this in a production environment.<\/p>\n

SET<\/span>\u00a0statistics<\/span>\u00a0time<\/span>\u00a0ON<\/span>
\nSET<\/span>\u00a0statistics<\/span>\u00a0io<\/span>\u00a0ON<\/span> <\/p>\n

DBCC<\/span>\u00a0freeproccache<\/span> <\/p>\n

DBCC<\/span>\u00a0dropcleanbuffers<\/span>\u00a0 <\/span><\/div>\n

SQL Server parse and compile time:
\nCPU time = 0 ms, elapsed time = 55 ms<\/strong>.<\/h6>\n
(3959 rows affected)
\nTable ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
\nTable ‘FactInternetSales’. Scan count 1, logical reads 345<\/strong>, physical reads 0, page server reads 0, read-ahead reads 344<\/strong>, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
\nTable ‘DimProduct’. Scan count 1, logical reads 6, physical reads 1, page server reads 0, read-ahead reads 11<\/strong>, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.<\/h6>\n
SQL Server Execution Times:
\nCPU time = 47 ms, elapsed time = 112 ms<\/strong>.<\/h6>\n

\nThere are two solutions to improve the performance with the Date_Bucket<\/strong> function:<\/p>\n