Row_Number()

  Yesterday I when I was looking into the enhancements in SQL server 2005 I discovered the below cool features and thought of sharing with your all. This new features will reduce the amount of coding and drastically improve the performance of complex SQL queries.  Conventionally we generate sequence numbers using complex logic or by using temp tables or table variable with identity columns, but with this new feature “ROW_NUMBER()” will allow developers to generate sequence numbers in a single line of code.

Below I have attached an example.

ROW_NUMBER()returns the sequence 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 ( [ <partition_by_clause> ] <order_by_clause> )

Example usage:

CREATE TABLE RowNumTest (Name VARCHAR(50), DOB DATETIME, Age AS (DATEDIFF(YEAR,DOB,GETDATE())))

INSERT INTO RowNumTest (Name, DOB) VALUES('Sathya', '1983-11-02')

INSERT INTO RowNumTest (Name, DOB) VALUES('Sathya Prakash', '1983-11-02')

INSERT INTO RowNumTest (Name, DOB) VALUES('Sathya Prakash.G', '1983-11-02')

INSERT INTO RowNumTest (Name, DOB) VALUES('Prakash', '1983-11-02')

SELECT

Id,

Name,

DOB,

Age

FROM

RowNumTest

Result set1(With No ROW_NUMBER())

Id          Name                                               DOB                                                             Age

----------- -------------------------------------------------- ----------------------- ----------------------------

1           Sathya                                             1983-11-02 00:00:00.000               25

2           Sathya Prakash                           1983-11-02 00:00:00.000                25

3           Sathya Prakash.G                       1983-11-02 00:00:00.000                25

4           Prakash                                          1983-11-02 00:00:00.000                25

 

SELECT

  Id,

ROW_NUMBER() OVER (ORDER BY Name Desc) As SeqNumber,

Name,

DOB,

Age

FROM

RowNumTest

Result set2 (With ROW_NUMBER() active)

Id          SeqNumber            Name                                               DOB                                                 Age

-------------------------------------------------------------------------------------------------------------------------------

3           1                              Sathya Prakash.G                 1983-11-02 00:00:00.000         25

2           2                              Sathya Prakash                     1983-11-02 00:00:00.000         25

1           3                              Sathya                                       1983-11-02 00:00:00.000         25

4           4                              Prakash                                    1983-11-02 00:00:00.000         25