SQL Server

The Difference Between ROW_NUMBER(), RANK(), and DENSE_RANK()

ROW_NUMBER() : assigns unique numbers to each row within the PARTITION given the ORDER BY clause. So you’d get

SELECT EmpId, ROW_NUMBER() OVER(ORDER BY EmpId) as RowNumber FROM Employee

RANK() : behaves like ROW_NUMBER(), except that “equal” rows are ranked the same

SELECT EmpId, RANK() OVER(ORDER BY EmpId) as RowNumber FROM Employee

DENSE_RANK() : This function is similar to Rank with only difference, this will not leave gaps between groups.

SELECT EmpId, DENSE_RANK() OVER(ORDER BY EmpId) as RowNumber FROM Employee

This query explains usage of all the four function together in one query.

SELECT Names,

     Grade,

     ROW_NUMBER () OVER (ORDER BY Grade DESC) as ROW_NUMBER,

       RANK () OVER (ORDER BY Grade DESC) as RANK,

       DENSE_RANK () OVER (ORDER BY Grade DESC) as DENSE_RANK

FROM dbo.Grades

How can we determine if a column is an identity column? Using OBJECTPROPERTY function, one can find if the table has an identity column. Syntax: OBJECTPROPERTY(object_id,proeprty) Here the property will be Table has Identity. SQL Server returns 1 if the table has an identity column, 0 if it doesn't. select columnproperty(object_id('Table_Name'),'Column_Name','IsIdentity')