Dualcore DBA pushes a predicate:
In this post, we’ll look at some interesting behaviour I’ve stumbled across with window functions and derived datasets in SQL Server.
I came across a query in a workload that was looking to get the most recent thing per group, a representative example in the Stack Overflow 2010 Database is below. The database is provided under cc-by-sa 4.0 licence from Stack Exchange Data Dump, I am running in compatibility level 150 on a SQL Server 2022 instance installed on a VM with 8 cores and 35GB RAM, though the issue being illustrated also occurs in earlier compatibility levels.
Click through for the demonstration and to see what happens when you switch to compatibility level 160.