Great article Greg. Concise, informative and to-the-point. I love Window Ranking Functions and could not live with out them.
One minor correction:
A ranking value is just a number, which typically starts at 1 and increments by one for each new ranking value.
This is not true in the case DENSE_RANK - it increments by the preceding number of ties +1; if the preceding value is unique then it increments by 1. And I think it would be more accurate to say that the ranking value always start at 1.
Its also worth noting that, although these four functions are called "Ranking functions", RANK and DENSE_RANK are slightly different than ROW_NUMBER and NTILE. For example, RANK and DENSE_RANK are always deterministic whereas ROW_NUMBER and NTILE are only deterministic when they are sorted by a column(s) with distinct values. Itzik Ben-Gan talks about this here. That's just my 2 cents.
Again, great work sir.
-- Itzik Ben-Gan 2001