How to do pagination in SQL

In our previous post we learned about Row_Number() function SQL.

Now consider, we some 100 records in the table, and you required to display the record using pagination, meaning, you have to display only 10 records at a time and when you click on the next tab in the UI, the same procedure should return the next records, i.e., the records starting from 11 to 20.

How can we achieve this?

Recollect our previous example,

CREATE TABLE [dbo].[TESTTABLE]( [STATUS] [nvarchar](50) NULL )

Insert few more records in that table, and make the table have more than 10 row in it.

INSERT INTO TESTTABLE VALUES ('MOHAN'),('RAJ'),('GANESH'),('JOHN'),('JIJO'),('ROCK'),('MELLON'),
(
'MOHANA'),('RAJA'),('GANESHA'),('JOHNNOBEL'),('JENSI'),('RICKTER'),('ROSE')
Now after inserting these records, our table would have in total 17 rows.

Ok our work starts here..

Just apply the Row_Number() function at first as below,

SELECT ROW_NUMBER() OVER(ORDER BY STATUS) [ROW], * FROM TESTTABLE

This will give the serial number of the records from 1 to 17.

Now, put the above query as a Common Table Expression [Learn about CTE here... MSDN] as below,

WITH CTE AS(SELECT ROW_NUMBER() OVER(ORDER BY STATUS) [ROW], * FROM TESTTABLE)SELECT * FROM CTE WHERE ROW > 11
Now whats the result?

ROW                   STATUS
--------                   --------------
12                        RAJ
13                        RAJA
14                        RICKTER
15                        ROCK
16                        ROSE
17                        SIVA
thats it............ we have achieved our pagination.

Now, all that you have to do is, write a logic to get the next tab count, which gives you exact place of fetch i.e., which set of record need to be fetched. Once that is done our sql query gives our desired pagination result.
Here it goes......

DECLARE @NOOFROWS AS INT SET @NOOFROWS = 5; --No of records per page
DECLARE @TABCOUNT AS INT SET @TABCOUNT = 2; --Next tab count
WITH CTE AS(SELECT ROW_NUMBER() OVER(ORDER BY STATUS) [ROW], * FROM TESTTABLE)SELECT * FROM CTE WHERE ROW
> (@NOOFROWS * (@TABCOUNT - 1)) AND ROW
<= (@TABCOUNT * @NOOFROWS)

Now this will give you the result as below,

ROW            STATUS
---------           -------------
6                  JIJO
7                  JOHN
8                  JOHNNOBEL
9                  MELLON
10                MOHAN