It all started with SQL Server 2005 which introduced the OVER-clause. It was (back then) possible to add a row number to a dataset by using something similair to:
You were also able to use the Ranking functions, such as ROW_NUMBER, RANK, DENSE_RANK and NTILE.
Now with SQL Server 2012, Microsoft introduced a few more so called ‘Window functions’ (aggregate and analytic):
Download my cheat sheet here (in Word format)