Let us first set up the environment for performing the demonstration.We will populate 100 records to a tblPagingExperiment table
--Drop the table tblPagingExperiment if it exists
IF OBJECT_ID('tblPagingExperiment','U') IS NOT NULL BEGIN
DROP TABLE tblPagingExperiment
END
GO
-- Create the table
Create Table tblPagingExperiment (
[Person ID] Int Identity
,[Person Name] Varchar(100)
,Age Int
,DOB Datetime
,Address Varchar(100)
)
GO
-- Populate 100 data to the table
Insert into tblPagingExperiment
Select
'Person Name' + CAST(Number AS VARCHAR)
, Number
,DATEADD(D,Number, '1900-01-01')
,'Address' + CAST(Number AS VARCHAR)
From master..spt_values
Where Type = 'p'
And Number Between 1 and 100
-- Project the records
Select *
From tblPagingExperiment
Paging using Sql Server 2005 Row_Number approach
-- Variable to hold the offset value
Declare @RowSkip As int
-- Variable to hold the fetch value
Declare @RowFetch As int
--Set the value of rows to skip
Set @RowSkip = 10
--Set the value of rows to fetch
Set @RowFetch = 20
;With Cte As
(
Select
rn=ROW_NUMBER()
Over(Order by (Select 1) /* generating some dummy column*/ )
,*
From tblPagingExperiment
)
-- Fetch the records from 11 to 30
Select
[Person ID]
,[Person Name]
,Age
,DOB
,Address
From Cte
Where rn Between (@RowSkip+1)And (@RowSkip+ @RowFetch)
/* Result */
Person ID Person Name Age DOB Address
11 Person Name11 11 1900-01-12 00:00:00.000 Address11
12 Person Name12 12 1900-01-13 00:00:00.000 Address12
13 Person Name13 13 1900-01-14 00:00:00.000 Address13
14 Person Name14 14 1900-01-15 00:00:00.000 Address14
15 Person Name15 15 1900-01-16 00:00:00.000 Address15
16 Person Name16 16 1900-01-17 00:00:00.000 Address16
17 Person Name17 17 1900-01-18 00:00:00.000 Address17
18 Person Name18 18 1900-01-19 00:00:00.000 Address18
19 Person Name19 19 1900-01-20 00:00:00.000 Address19
20 Person Name20 20 1900-01-21 00:00:00.000 Address20
21 Person Name21 21 1900-01-22 00:00:00.000 Address21
22 Person Name22 22 1900-01-23 00:00:00.000 Address22
23 Person Name23 23 1900-01-24 00:00:00.000 Address23
24 Person Name24 24 1900-01-25 00:00:00.000 Address24
25 Person Name25 25 1900-01-26 00:00:00.000 Address25
26 Person Name26 26 1900-01-27 00:00:00.000 Address26
27 Person Name27 27 1900-01-28 00:00:00.000 Address27
28 Person Name28 28 1900-01-29 00:00:00.000 Address28
29 Person Name29 29 1900-01-30 00:00:00.000 Address29
30 Person Name30 30 1900-01-31 00:00:00.000 Address30