SQL Server Rank functions, RANK, NTILE, DENSE_RANK, ROW_NUMBER

Transact-SQL provides the following ranking functions:

-RANK
-NTILE
-DENSE_RANK
-ROW_NUMBER

Rank() - Applies to monotonically increasing number for each value in the set
Row_Number() - simply assigns sequential numbering to the records of a result-set or to the records within groups of a result-set
Rank() and Row_Number differ only if there are ties.
Dense_Rank() - Method the rank is increasing number for each value in the set.
NTILE(n)- Which splits the records into partition based on the Column in the Over() and assigns the Rank for the partition.

OVER clause is required in all the ranking functions and with that you specify the partitioning and ordering of records before the ranking functions are evaluated. If you don't specify it, you will get an error similar to "Incorrect syntax near 'ROW_NUMBER', expected 'OVER'."
ORDER BY clause is mandatory and if you don't specify it you will get an error similar to "The ranking function "ROW_NUMBER" must have an ORDER BY clause.". With this clause you specify a column or a set of columns which will be used to order the records within a result-set or within record groups/partitions of a result-set. Please note you can specify only those columns which are being made available by the FROM clause of the query.

Please find the below Image for the example:


Click to get full Image