Now that SQL Server 2022 support is here, there is an opportunity to make use of DATE_BUCKET and/or DATETRUNC to allow for date/time grouping that can make use of the sorting of an appropriate index. While the deployments of SQL Server 2022 may be few, all Azure SQL PaaS databases can make use of this already.
As an example: An Aggregate table derived from a transactional table where you want to create monthly aggregates for timestamped transactions will currently always require either an explicit Sort or HashMatch operation in the query plan (boilerplate SQL removed from the code TX generates):
There is an index here on [OrderDate] Including [LineTotal] but the Index Scan operator is using False for the Ordered property - I have a separate idea on automatically generating covering indexes for Aggregate tables.
If you make use of DATE_BUCKET, you see that the query plan can make use of Stream Aggregate and uses an Ordered Index Scan:
The resulting plan costs less than 50% of the original plan. See: https://sqlperformance.com/2022/10/t-sql-queries/date-bucket-datetrunc-improve-time-based-grouping
