In this article, we will discuss very simple but much required topic for daily use in SQL Server or for preparation for MCTS SQL Server 2008 Developer (70-451). Let’s start one by one with these:
1) ROW_NUMBER: assigns numbers from 1 to N on the basis of sorting order specified by the user. ROW_NUMBER never accounts for ties in the result set. The rows with the same values within the column that is specified in order by, can produce different row numbering.
2) RANK: RANK can be same as ROW_NUMBER, provided no ties in the result set. However, if ties occur in result set, RANK will assign same value to each row and then assigns the next value, leaving a gap in t he sequence with respect to number of rows tied.
3) DENSE_RANK: It works same as RANK, but does not leave gap in the sequence with repect to the number of rows tied.
4) NTILE: It divides result set into equal number of groups. Suppose you have 54 values and you use NTILE(5); it will make 4 groups of 11 values and 1 group of 10 values.
Very short but very useful concept of SQL. It can gain you marks in your certification ;).
