Click here to Skip to main content
11,720,557 members (71,694 online)
Click here to Skip to main content

Tagged as

Paging in MS SQL Server 2005/2008 using ROW_NUMBER()

, 21 Oct 2013 CPOL 22.9K 7
Rate this:
Please Sign up or sign in to vote.
Paging in MS SQL Server 2005/2008 using ROW_NUMBER()

Sometimes we need to implement paging functionality in our database. We can use ROW_NUMBER() function for this purpose. ROW_NUMBER function is one of the ranking function of SQL Server. Here is an example of ROW_NUMBER -

SELECT *     FROM  (SELECT  ROW_NUMBER() OVER(ORDER BY EmpID) AS Row, 
      * FROM Employee AS tbl ) WHERE  Row >= 1 AND Row <= 10

ROW_NUMBER is used for assigning sequential numbers to the records of record-set. In the above query a sequel number is assigned to each record of the Employee table.

Here we have used following two clause -

  1. OVER clause - It is used for specifying the ordering of records before assigning the sequence to each record.
  2. ORDER clause -  In order clause we define column or columns which is used to order the records in the record-sets. In our case we have used EmpId with ORDER clause, so first each record of Employee table is first ordered  according to EmpId and then sequence number is assigned to each record.
*PARTITION clause can also be used along with OVER and ORDER clause (optional). Partition clause allows user to create some partition or sub record-sets as per the given column.

Once a sequence number is assigned to each record of the Employee table, then we can use each Row of record-set as one page.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Vivek Goyal
Software Developer (Senior)
India India
Software Engineer having hands-on experience with C, C++, C#, .NET, ASP.NET, SQL, Website designing technologies, Joomla CMS, Application development, COM, MFC, Installshield, Installscript project, Basic MSI

http://newapputil.blogspot.in/
http://nvivekgoyal.blogspot.in/

You may also be interested in...

Comments and Discussions

 
QuestionGiven Example is not working Pin
Badri.nath22-Jun-15 17:11
memberBadri.nath22-Jun-15 17:11 
AnswerRe: Given Example is not working Pin
Vivek Goyal22-Jun-15 18:41
memberVivek Goyal22-Jun-15 18:41 
QuestionThanks Pin
di~v~inci11-Aug-14 22:42
memberdi~v~inci11-Aug-14 22:42 
AnswerRe: Thanks Pin
Vivek Goyal22-Jun-15 19:30
memberVivek Goyal22-Jun-15 19:30 
GeneralMy vote of 1 Pin
Patel Shweta29-Jan-14 20:40
memberPatel Shweta29-Jan-14 20:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150901.1 | Last Updated 21 Oct 2013
Article Copyright 2013 by Vivek Goyal
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid