Actually, I only submitted the winning entry;
Denali CTP3 SQL Server 2012 is the real winner. After having a blog posted here recently about running totals performance in Denali CTP3 SQL Server 2012, I happened to run across this T-SQL Challenge for doing running totals. On a lark, I posted a Denali CTP3 SQL Server 2012 solution. I fully expected it to be discounted (on a not released version of SQL, other solutions would be faster, heck, anything)… but this simple solution turned out to completely trounce all of the other solutions and it was selected as the top winner! The SQL Server 2012 solution came in with 529 reads, no writes… some of the other solutions that produced the correct answer (and thus became a “winning” solution) came in with MILLIONS of reads, and some with THOUSANDS of writes. Duration? The closest competitor came in at twice the time. Honestly, I’d hate to see what the performance is of some of the other solutions that produced the correct results, but were not performing well enough to make it into the top echelon.
What I’d like is for you to first check out the specifics of the challenge here, then check out all of the winning solutions here. Check out how simply and elegantly SQL Server 2012 lets us do this rather complicated calculation, and the performance stats it produced. Then, check out all of the other solutions. Look at how complicated the code can get, and glance briefly (a longer look might frighten you!) at the performance stats of those solutions. Remember, these other winning solutions are the best performing solution that can be devised on prior versions of SQL Server within the rules of the challenge that produces the correct results.
So, for all those fine folks at Microsoft that implemented the ANSI windowing: thank you for what you did. This just goes to show that this can now enable you to write simpler, faster code quicker than ever before. Thanks!!!