Calculating percentiles with SQL Server has not been easy. At least not to what it became in SQL Server 2012.
The code is easy to read and the syntax is understandable. How did we write the percentile calculation before SQL Server 2012?
It required a lot more code, but it does provide with exact same answer.
How does these two algorithms perform?
That is quite a difference! The 2005 algorithm is almost twice as fast and uses 27% less CPU. This is somewhat disturbing so I looked at the execution plan and figured out how to write an ever more efficient percentile calculation algorithm.
And this is the statistics
Look at that beauty. No worktable at all, uses 70% less CPU than the SQL Server 2012 version and finishes in 91% less time.