Alex Ionine (6/16/2008)
it could be a descent article had it been written 2 years ago.
I have to disagree. Given SQL Server 2005's age now, I would not have emphasized the "newness" of the feature personally, but it is a good article at any time.
It highlights a seldom used feature which I suspect many developers do not know of and provides a good explanation and good examples. It then provides an alternate solution to the same problem (also using a technique I suspect many developers are not familiar with) and discusses why one might be preferable over the other. I found it informative personally.
As a side note, you might want to think about refactoring the partitioned solution at the bottom with a CTE. I know it is a matter of opinion, but personally, I find it much easier to read when the subqueries are separated from the main query and it provides a more clear delineation as tot he different parts of the query if someone is skimming over it quickly.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/