Pages

Wednesday, October 2, 2013

Paging In SQL Server with derived table and CTE

First We will create table

create table RecordTable(col1 int, col2 int)

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.

No comments:

Post a Comment