SQL Server 2005 and above has total of 4 ranking function. Ranking functions return a ranking value for each row in a partition. All the ranking functions are non-deterministic.

ROW_NUMBER () OVER ([] ) Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

RANK () OVER ([] ) Returns the rank of each row within the partition of a result set.

DENSE_RANK () OVER ([] ) Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

NTILE (integer_expression) OVER ([] ) Distributes the rows in an ordered partition into a specified number of groups

========== Putting it all together, the following query shows all ranking functions in action using the famous AdventureWorks OLTP database!

SELECT i.ProductID 
  , ProductName = p.Name 
  , i.LocationID 
  , i.Quantity 
  , RowNumber = ROW_NUMBER() 
                  OVER (PARTITION BY i.LocationID 
                        ORDER BY i.Quantity) 
  , Quartile = NTILE(4) 
                  OVER (PARTITION BY i.LocationID 
                        ORDER BY i.Quantity) 
  , Rank = RANK() 
                  OVER (PARTITION BY i.LocationID 
                        ORDER BY i.Quantity) 
  , DenseRank = DENSE_RANK() 
                  OVER (PARTITION BY i.LocationID 
                        ORDER BY i.Quantity) 
 FROM Production.ProductInventory i 
       INNER JOIN Production.Product p 
             ON i.ProductID = p.ProductID 
 WHERE i.LocationID in (3, 4, 5) 
 ORDER BY i.LocationID, RowNumber;

----- here comes the query result ----- (ProductName is intentionally removed for better presentation)

ProductID   LocID Quantity RowNumber  Quartile Rank DenseRank
----------- ----- -------- ---------- ----- ---------- ------
492         3     17       1          1     1          1
496         3     30       2          1     2          2
493         3     41       3          2     3          3
494         3     49       4          3     4          4
495         3     49       5          4     4          4
494         4     12       1          1     1          1
492         4     14       2          1     2          2
493         4     24       3          2     3          3
496         4     25       4          3     4          4
495         4     35       5          4     5          5
317         5     158      1          1     1          1
318         5     171      2          1     2          2
351         5     179      3          1     3          3
319         5     184      4          1     4          4
952         5     192      5          1     5          5
400         5     260      6          1     6          6
815         5     265      7          1     7          7
401         5     283      8          1     8          8
352         5     300      9          1     9          9
488         5     318      10         1     10         10
477         5     323      11         1     11         11
476         5     324      12         1     12         12
949         5     336      13         1     13         13
487         5     337      14         2     14         14
950         5     342      15         2     15         15
332         5     344      16         2     16         16
945         5     347      17         2     17         17
948         5     347      18         2     17         17
951         5     348      19         2     19         18
802         5     350      20         2     20         19
803         5     356      21         2     21         20
804         5     363      22         2     22         21
399         5     366      23         2     23         22
398         5     372      24         2     24         23
320         5     372      25         2     24         23
484         5     374      26         2     26         24
481         5     374      27         3     26         24
479         5     390      28         3     28         25
816         5     406      29         3     29         26
327         5     408      30         3     30         27
819         5     409      31         3     31         28
482         5     427      32         3     32         29
485         5     427      33         3     32         29
818         5     428      34         3     34         30
821         5     432      35         3     35         31
817         5     443      36         3     36         32
820         5     446      37         3     37         33
486         5     515      38         3     38         34
480         5     515      39         3     38         34
483         5     531      40         4     40         35
316         5     532      41         4     41         36
321         5     540      42         4     42         37
330         5     548      43         4     43         38
329         5     558      44         4     44         39
328         5     568      45         4     45         40
323         5     568      46         4     45         40
324         5     568      47         4     45         40
478         5     568      48         4     45         40
331         5     574      49         4     49         41
322         5     587      50         4     50         42
350         5     622      51         4     51         43

==========

SQL Server 2012 introduces new analytical functions PERCENT_RANK(). This function returns relative standing of a value within a query result set or partition.

The formula to find PERCENT_RANK() is as following:

PERCENT_RANK() = (RANK() – 1) / (Total Rows – 1)

What is Ranking Functions

Ranking functions are functions that allow you to sequentially number your result set.

These functions can be used to provide you with a number of different sequential numbering schemes. For example you can number each row in your result set sequentially where the first row has a ranking number of 1, the second row has a ranking of 2, third row has 3, and so on. You can also use these ranking functions to sequentual number groups, so each group would have a numbering scheme of 1,2, 3, and then the next group would start over with 1, 2, 3, etc..

RANKING functions inside SQL Server 2008 R2

RANK

Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

Syntax: RANK ( ) OVER ( [ < partitionbyclause > ] < orderbyclause > )

NTILE

Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

Syntax: NTILE (integerexpression) OVER ( [ ] < orderby_clause > )

integerexpression can only reference columns in the PARTITION BY clause. integerexpression cannot reference columns listed in the current FROM clause.

DENSE_RANK

Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

Syntax: DENSERANK ( ) OVER ( [ ] < orderby_clause > )

ROW_NUMBER

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Syntax: ROW_NUMBER ( ) OVER ( [ ] )

Divides the result set produced by the FROM clause into partitions to which the above functions is applied.

Determines the order in which the function value is assigned to the rows in a partition.

Example 1 :: 

USE AdventureWorks2008R2;
GO

SELECT p.FirstName, p.LastName
    ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS 'Row Number'
    ,RANK() OVER (ORDER BY a.PostalCode) AS 'Rank'
    ,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS 'Dense Rank'
    ,NTILE(4) OVER (ORDER BY a.PostalCode) AS 'Quartile'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Person p 
        ON s.BusinessEntityID = p.BusinessEntityID
    INNER JOIN Person.Address a 
        ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

        /*
    Example 2: 

    The following statements create the sample table, and insert  a few sample records for demonstration purposes.
        */

        CREATE TABLE SampleData
        (
            RecordId INT IDENTITY(1,1),
            TypeId INT,
            ActivityDate DATETIME,
            MessageText TEXT
        )
        GO

        INSERT INTO SampleData
            (TypeId, ActivityDate, MessageText)
        VALUES
            (1, '10/1/2008', 'Test Message (Max for Cat 1)')

        INSERT INTO SampleData
            (TypeId, ActivityDate, MessageText)
        VALUES
            (1, '9/28/2008', 'Test Message (Other for Cat 1)')

        INSERT INTO SampleData
            (TypeId, ActivityDate, MessageText)
        VALUES
            (2, '10/15/2008', 'Test Message (Max for Cat 2)')

        GO

        /*
            Use the CTE Expression to get the list of ranked
        entries from the system, partition by to group by type
        */
        WITH RankedData AS
        (
            SELECT
            	SD.RecordId,
            	SD.TypeId,
            	SD.ActivityDate,
            	SD.MessageText,
                Rank() OVER 
            		(PARTITION BY SD.TypeId 
            			ORDER BY SD.ActivityDate DESC) AS ActivityRank
            FROM SampleData SD
        )
        --Now select results
        SELECT
            RankedData.RecordId,
            RankedData.TypeID,
            RankedData.ActivityDate,
            RankedData.MessageText
        FROM RankedData
        WHERE RankedData.ActivityRank = 1
        ORDER BY TypeId

        /*
Similarly we can apply for all other ranking functions.
       */

        GO

**

Here is the list of new built-in Transact-SQL Functions in SQL Server 2012

**

Conversion functions

* PARSE
* TRY_CONVERT
* TRY_PARSE

Date and time functions

* DATEFROMPARTS
* DATETIME2FROMPARTS
* DATETIMEFROMPARTS
* DATETIMEOFFSETFROMPARTS
* EOMONTH
* SMALLDATETIMEFROMPARTS
* TIMEFROMPARTS

Logical functions

* CHOOSE
* IIF

String functions

* CONCAT
* FORMAT

Analytical Function

* CUME_DIST 
* FIRST_VALUE 
* LAST_VALUE 
* LEAD 
* LAG 
* PERCENTILE_CONT
* PERCENTILE_DISC

**

2012 New SQL Functions Click

Study Ref. Click 1::

Examples Ref. Click 2::

Best Examples Ref. Click 3 ::**

Thanks

Yogesh

Rank,NTILE,DENSE_RANK and ROW Number are the ranking functions in SQL 2008 R2.

Couple of new functions in SQL 2012 are-

Conversion functions

view sourceprint?1.* PARSE 2.* TRYCONVERT 3.* TRYPARSE Date and time functions

1.DATEFROMPARTS 2.DATETIME2FROMPARTS 3.DATETIMEFROMPARTS 4.DATETIMEOFFSETFROMPARTS 5.EOMONTH 6.SMALLDATETIMEFROMPARTS 7.TIMEFROMPARTS

Logical functions

1.CHOOSE 2.IIF

String functions

1.* CONCAT 2.* FORMAT

Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.

In SQL Server 2008 R2 Transact-SQL provides the following ranking functions:

1. RANK
2. DENSE_RANK
3. NTILE
4. ROW_NUMBER

http://blog.sqlauthority.com/2007/10/09/sql-server-2005-sample-example-of-ranking-functions-rownumber-rank-denserank-ntile/ http://msdn.microsoft.com/en-us/library/ms189798(v=SQL.105).aspx

Microsoft SQL Server 2012 introduces 14 new built-in functions. These new functions are:

Conversion functions

• PARSE
• TRY_CONVERT
• TRY_PARSE

Date and time functions

• DATEFROMPARTS
• DATETIME2FROMPARTS
• DATETIMEFROMPARTS
• DATETIMEOFFSETFROMPARTS
• EOMONTH
• SMALLDATETIMEFROMPARTS
• TIMEFROMPARTS

Logical functions

• CHOOSE
• IIF

String functions

• CONCAT
• FORMAT

http://blog.sqlauthority.com/2011/09/06/sql-server-denali-conversion-function-parse-a-quick-introduction/ http://blog.sqlauthority.com/2011/09/19/sql-server-denali-date-and-time-functions-datefromparts-datetimefromparts-datetime2fromparts-timefromparts-smalldatetimefromparts-datetimeoffsetfromparts-a-quick-introduc/ http://blog.sqlauthority.com/2010/11/25/sql-server-concat-function-in-sql-server-sql-concatenation/ http://blog.sqlauthority.com/2011/09/11/sql-server-denali-logical-function-choose-a-quick-introduction/

There are 4 ranking functions in SQL server 2008

1. ROW_NUMBER()
2. RANK()
3. DENSE_RANK()
4. NTILE()

There around 21 new ranking functions in SQL 2012 besides old 4 ranking functions in SQL 2008

1. PARSE()
2. TRY_CONVERT()
3. TRY_PARSE()
4. CONCAT()
5. FORMAT()
6. IIF()
7. CHOOSE()
8. EOMONTH()
9. TIMEFROMPARTS()
10. SMALLDATETIMEFROMPARTS()
11. DATEFROMPARTS()
12. DATETIMEFROMPARTS()
13. DATETIME2FROMPARTS()
14. DATETIMEOFFSETFROMPARTS()
15. CUME_DIST()
16. FIRST_VALUE()
17. LAST_VALUE()
18. LAG()
19. LEAD()
20. PERCENTILE_DISC()
21. PERCENTILE_CONT()

SQL Server 2005 and above has total of 4 ranking function. Ranking functions return a ranking value for each row in a partition. All the ranking functions are non-deterministic.

ROW_NUMBER () OVER ([] ) Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

RANK () OVER ([] ) Returns the rank of each row within the partition of a result set.

DENSE_RANK () OVER ([] ) Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

NTILE (integer_expression) OVER ([] ) Distributes the rows in an ordered partition into a specified number of groups

SQL Server 2012 introduces new analytical functions PERCENT_RANK(). This function returns relative standing of a value within a query result set or partition.

The formula to find PERCENT_RANK() is as following:

PERCENT_RANK() = (RANK() – 1) / (Total Rows – 1)

Other Functions in SQL Server 2012

Conversion functions

1.* PARSE

2.* TRY_CONVERT

3.* TRY_PARSE

Date and time functions

1.* DATEFROMPARTS

2.* DATETIME2FROMPARTS

3.* DATETIMEFROMPARTS

4.* DATETIMEOFFSETFROMPARTS

5.* EOMONTH

6.* SMALLDATETIMEFROMPARTS

7.* TIMEFROMPARTS

Logical functions

1.* CHOOSE

2.* IIF

String functions

1.* CONCAT

2.* FORMAT

Analytical Function

1.* CUMEDIST
2.* FIRSTVALUE
3.* LASTVALUE
4.* LEAD
5.* LAG
6.* PERCENTILECONT 7.* PERCENTILE_DISC

Rank()

1. DENSE_RANK()

2.Row_Number() NTILE()

New functions in sql server 2012 TRYPARSE() : select tryparse('2011-02-30' as date) OUTPUT :NULL PARSE() : SELECT PARSE('13-12-2011' AS datetime USING 'en-US') AS Result IT THROWS ERROR because it means the 13th month

TRY_CONVERT() : This is pretty much the same as the well known CONVERT, but again with the NULL output for invalid input strings. SELECT CONVERT(XML, 'Hello World!) AS MyXML FROM ABOVE QUERY IT RETURNS "NULL".

CONCAT() FORMAT() IIF() CHOOSE()

DATEFROMPARTS () DATETIME2FROMPARTS () TIMEFROMPARTS () EOMONTH()

SQL Server 2008 R2 has total of 4 ranking function. Ranking functions return a ranking value for each row in a partition. All the ranking functions are non-deterministic.

ROW_NUMBER () OVER ([] )
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

RANK () OVER ([] )
Returns the rank of each row within the partition of a result set.

DENSE_RANK () OVER ([] )
Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

NTILE (integer_expression) OVER ([] )
Distributes the rows in an ordered partition into a specified number of groups.

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
,ROW_NUMBER() OVER (
ORDER BY a.PostalCode) AS 'Row Number'
,RANK() OVER (
ORDER BY a.PostalCode) AS 'Rank'
,DENSE_RANK() OVER (
ORDER BY a.PostalCode) AS 'Dense Rank'
,NTILE(4) OVER (
ORDER BY a.PostalCode) AS 'Quartile'
,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
INNER JOIN Person.Contact c
ON s.SalesPersonID = c.ContactID
INNER JOIN Person.Address a
ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;

http://blog.sqlauthority.com/2007/10/09/sql-server-2005-sample-example-of-ranking-functions-rownumber-rank-dense_rank-ntile/

New functions introduced inside SQL Server 2012

Conversion functions

PARSE  
TRY_CONVERT  
TRY_PARSE

Date and time functions

DATEFROMPARTS  
DATETIME2FROMPARTS  
DATETIMEFROMPARTS  
DATETIMEOFFSETFROMPARTS  
EOMONTH  
SMALLDATETIMEFROMPARTS  
TIMEFROMPARTS

Logical functions

CHOOSE  
IIF

String functions

CONCAT  
FORMAT

http://blog.sqlauthority.com/2011/09/21/sql-server-denali-14-new-functions-a-quick-guide/

Analytical functions

CUME_DIST  
FIRST_VALUE  
LAST_VALUE  
LEAD  
LAG  
PERCENTILE_CONT  
PERCENTILE_DISC  
PERCENT_RANK

http://www.codeproject.com/Articles/290251/SQL-SERVER-2012-Summary-of-All-the-Analytic-Functi

Following are the Ranking functions in SQL Server 2008 R2;

RANK

Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question

If two or more rows tie for a rank, each tied rows receives the same rank. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number three, because there are two rows that are ranked higher. Therefore, the RANK function does not always return consecutive integers.

The sort order that is used for the whole query determines the order in which the rows appear in a result set.

DENSE_RANK

Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

If two or more rows tie for a rank in the same partition, each tied rows receives the same rank. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number two. This is one more than the number of distinct rows that come before this row. Therefore, the numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks.

The sort order used for the whole query determines the order in which the rows appear in a result. This implies that a row ranked number one does not have to be the first row in the partition.

NTILE

Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

If the number of rows in a partition is not divisible by integer_expression, this will cause groups of two sizes that differ by one member. Larger groups come before smaller groups in the order specified by the OVER clause. For example if the total number of rows is 53 and the number of groups is five, the first three groups will have 11 rows and the two remaining groups will have 10 rows each. If on the other hand the total number of rows is divisible by the number of groups, the rows will be evenly distributed among the groups. For example, if the total number of rows is 50, and there are five groups, each bucket will contain 10 rows.

ROWNUMBER

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition.

Ranking Functions in SQL Server R2 Are:

• ROWNUMBER()OVER([partitionbyclause] [orderbyclause]) : Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. ROWNUMBER does not account for ties within the result set, so if you have rows with the same values within the column(s) that you are ordering by, repeated calls to the database for the same result set can produce different row numbering.
• RANK()OVER([partitionbyclause] [orderbyclause]): Returns the rank of each row within the partition of a result set. If two or more rows tie for a rank, each tied rows receives the same rank. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number three, because there are two rows that are ranked higher. Therefore, the RANK function does not always return consecutive integers.
• DENSERANK()OVER([partitionbyclause] [orderbyclause]) :Returns the rank of rows within the partition of a result set, without any gaps in the ranking. f two or more rows tie for a rank in the same partition, each tied rows receives the same rank. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number two. This is one more than the number of distinct rows that come before this row. Therefore, the numbers returned by the DENSERANK function do not have gaps and always have consecutive ranks.
• NTILE(integerexpression)OVER([partitionbyclause] [orderbyclause]): Distributes the rows in an ordered partition into a specified number of groups. If the number of rows in a partition is not divisible by integerexpression, this will cause groups of two sizes that differ by one member. Larger groups come before smaller groups in the order specified by the OVER clause. For example if the total number of rows is 53 and the number of groups is five, the first three groups will have 11 rows and the two remaining groups will have 10 rows each. If on the other hand the total number of rows is divisible by the number of groups, the rows will be evenly distributed among the groups. For example, if the total number of rows is 50, and there are five groups, each bucket will contain 10 rows.

Arguments

< partitionbyclause > : Divides the result set produced by the FROM clause into partitions to which the function is applied.

< orderbyclause >: Determines the order in which the RANK values are applied to the rows in a partition.

New Functions In SQL 2012

1.Conversion and Parsing:

New functions in this category: TRYCONVERT, PARSE and TRYPARSE.

TRY_CONVERT: Its like the existing CONVERT function, only when the input value isn’t convertible, instead of generating an error, the function returns a NULL.

PARSE: The PARSE function in essence does a conversion of an input string to the target type, but unlike CAST and CONVERT, it supports an optional USING clause indicating the culture.

TRYPARSE: TRYPARSE does the same as PARSE, only when the input isn’t converted to the target type, instead of generating an error, the function returns a NULL.

2.Date and Time

New functions in this category: EOMONTH, DATEFROMPARTS, DATETIME2FROMPARTS, DATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS, SMALLDATETIMEFROMPARTS and TIMEFROMPARTS.

EOMONTH:The EOMONTH function returns the end of month date corresponding to the input date and time value, with the time set to midnight, retaining the time zone if it exists.

%FROMPARTS : For each date and time data type, SQL Server Denali provides a FROMPARTS function that allows constructing a value of this type from integer parts. This is useful in general, but also important for migrations from environments like Excel, Access and others that support such functionality.

3.Logical

Functions in this category: CHOOSE and IIF.

CHOOSE:The CHOOSE function accepts an integer input followed by a list of values of any data type, and returns as output the value from the list in the position indicated by the first input. IIF:The IIF function is another function Access supports. It accepts as first input a predicate, as second input an expression to return in case the predicate is true, and as third input an expression to return in case the input is false or unknown.

4.String Manipulation

New functions in this category: CONCAT and FORMAT.

CONCAT:The CONCAT function concatenates the input values into a single result string. The CONCAT function converts NULL inputs to empty strings before concatenation.

FORMAT:The FORMAT function allows you to format an input value to a character string

5.Math

Enhanced function in this category: LOG.

LOG: SQL 2012 enhances the LOG function by supporting a second argument representing the base.

New Ranking Functions in SQL Server 2012

SQL Server Denali improves support for window aggregate functions by adding a window order clause and a window frame clause. This means that you can now restrict a subset of rows within the window partition. SQL Server supports a ROWS option that allows you to frame rows based on an offset from the current row in terms of number of rows. SQL Server also adds limited support for the RANGE option, which allows framing rows based on an offset from the current row in terms of values.

ROWS option, the following query issued against the sample database TSQL2012, computes the running total quantity for each employee and order month:

USE TSQL2012; 
SELECT empid, ordermonth, qty,
  SUM(qty) OVER(PARTITION BY empid
                ORDER BY ordermonth
                ROWS BETWEEN UNBOUNDED PRECEDING
                         AND CURRENT ROW) AS runqty
FROM Sales.EmpOrders;

SQL Server Denali adds support for window offset functions LAG and LEAD which allow returning elements from a preceding or a following row. It also supports the FIRSTVALUE and LASTVALUE functions which allow returning elements from the first or last row in a window frame. As an example, the following query returns for each customer order the value of the current, previous and next orders:

SELECT custid, orderdate, orderid, val,
  LAG(val)  OVER(PARTITION BY custid
                 ORDER BY orderdate, orderid) AS prevval,
  LEAD(val) OVER(PARTITION BY custid
                 ORDER BY orderdate, orderid) AS nextval
FROM Sales.OrderValues;

SQL Server Denali also introduces support for window distribution functions. It adds window rank distribution functions PERCENTRANK (for percentile rank) and CUMEDIST (for cumulative distribution). It also adds window inverse distribution functions PERCENTILEDISC (percentile using discrete distribution model) and PERCENTILECONT (percentile using continuous distribution model). The last two are implemented as window functions and not as grouped ordered set functions.

As an example, the following query computes the percentile rank and cumulative distribution of student scores per test:

SELECT testid, studentid, score,
  PERCENT_RANK() OVER(PARTITION BY testid ORDER BY score) AS percentrank,
  CUME_DIST()    OVER(PARTITION BY testid ORDER BY score) AS cumedist
FROM Stats.Scores;

Reference

http://www.sumitgarg.net/post/2010/04/23/Ranking-Functions-in-SQL-SERVER-2008.aspx

http://msdn.microsoft.com/en-us/library/ms189798.aspx

http://blog.sqlauthority.com/2007/10/09/sql-server-2005-sample-example-of-ranking-functions-rownumber-rank-dense_rank-ntile/

http://www.sqlmag.com/blog/puzzled-by-t-sql-blog-15/tsql/improved-support-window-functions-sql-server-denali-ctp3-140423

http://www.sqlmag.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-tsql-glance-enhanced-functions-140785