Paging In SQL Server with derived table and CTE

In this article we will check how can we do paging.

First We will create table

create table RecordTable(col1 int, col2 int)

Now we will insert records into the table.

insert into RecordTable

select 1,1

union all

select 1,2

union all

select 1,3

union all

select 1,4

union all

select 1,5

union all

select 1,6

union all

select 1,7

We will do it through in 2 ways.

1. With Derived Table

2. With Common Table Expression.

Mostly performance is same in both ways, in some scenario it may vary.

1.Paging with Derived Table

select * from(

select col1,col2,

ROW_NUMBER() over( order by col1)  as rowno

from RecordTable)

ctedup

where rowno>minNo and rowno<MaxNo

2. Paging with CTE

;with ctedup as(

select col1,col2,

ROW_NUMBER() over( order by col1)  as rowno

from duplicateRecordTable)

select * from ctedup

where rowno>MinNo and rowno<MaxNo

It will gives you records in between min and max no.

Happy Coding!!