Paging in SQL

Haven't posted anything for quite some time. This post is about how to make paging in SQL.

If you are interested, you can follow the steps and execute the query provided. Else, I also provide the source code. You can download and observe it later.

Before getting into the code, execute this query to create the tables first. 2 Tables are used.

create table dbo.MsUser
(  UserID char(20) primary key,
   Username varchar(100),
   Address varchar(50),
   DivisionID int
)

-- Create Table MsDivision
create table dbo.MsDivision
(  DivisionID int primary key,
   DivisionName varchar(50)
)

And here is the query to insert sample data:

-- Insert Data into MsDivision
insert into dbo.MsDivision values ( 1, 'IT' )
insert into dbo.MsDivision values ( 2, 'IS' )
insert into dbo.MsDivision values ( 3, 'Finance' )
insert into dbo.MsDivision values ( 4, 'Management' )

-- Insert Data into MsUser
insert into dbo.MsUser values ( 'Himura', 'Miss Himura', 'Earth', 3 )
insert into dbo.MsUser values ( 'Selvia', 'Selvia', 'Indonesia', 2 )
insert into dbo.MsUser values ( 'Superman', 'Clark Kent', 'Earth', 2 )
insert into dbo.MsUser values ( 'SelviaHimura', 'Selvia Himura', 'Earth', 4 )
insert into dbo.MsUser values ( 'Luck', 'Steven Luck', 'Indonesia', 1 )
insert into dbo.MsUser values ( 'SuperLuck', 'Super Luck', 'United States', 1 )
insert into dbo.MsUser values ( 'Selvi', 'Selvi', 'Indonesia', 1 )
insert into dbo.MsUser values ( 'Lucky', 'Lucky Luke', 'United States', 3 )
insert into dbo.MsUser values ( 'Steven', 'Steven', 'Earth', 4 )

Here are the data inserted for the tables:
Table MsDivision:

Table MsUser:

After creating the tables and inserting the data, execute the following query to select the data, sorted by the field Username.

-- Select All Data Sorted by Username
select a.UserID, a.Username, a.Address, a.DivisionID, b.DivisionName
from dbo.MsUser a
left join dbo.MsDivision b
on b.DivisionID = a.DivisionID
order by a.Username

Here is the result:




Here is the part about how to create Paging in SQL.

We will use the functionality of Stored Procedure to retrieve the data.
Here is the query of the stored procedure used to retrieve the paged data.

create procedure dbo.spr_GetSimplePaging
@PageSize int, -- @PageSize = item per page
@PageNo int -- @PageNo = current page
as
set nocount on

-- Create Temporary Table to Store the Raw Data
-- The content of the temporary table: An identity field which will be used to determine the paging
--    and the columns contained in the original table
create table #temp
(  UniqueID int identity, -- will be used to determine the paging
   UserID char(20),
   Username varchar(100),
   Address varchar(50),
   DivisionID int,
   DivisionName varchar(50)
)

-- Select the Raw Data into temporary table #temp
insert into #temp ( UserID, Username, Address, DivisionID, DivisionName )
select a.UserID, a.Username, a.Address, a.DivisionID, b.DivisionName
from dbo.MsUser a
left join dbo.MsDivision b
    on b.DivisionID = a.DivisionID
order by a.Username

-- Get Data Using Formula for Paging
select UserID, Username, Address, DivisionID, DivisionName
from #temp
where UniqueID between ( @PageNo - 1 ) * @PageSize + 1
and @PageNo * @PageSize

-- Drop temporary table
drop table #temp

There are 3 main parts in the data retrieval process:
1. Create a temporary table which will be used to store the raw data. The temporary table will consist of an identity field which will be used to determine the paging (we use a field named UniqueID in the query above) and the columns contained in the original table(s).
Here is the first part of the query:

create table #temp
(  UniqueID int identity, -- will be used to determine the paging
   UserID char(20),
   Username varchar(100),
   Address varchar(50),
   DivisionID int,
   DivisionName varchar(50)
)

2. Select the raw data into a temporary table. We name the temporary table #temp on the sample query above.

Here is the second part of the query:

insert into #temp ( UserID, Username, Address, DivisionID, DivisionName )
select a.UserID, a.Username, a.Address, a.DivisionID, b.DivisionName
from dbo.MsUser a
left join dbo.MsDivision b
 on b.DivisionID = a.DivisionID
order by a.Username

3. Get the data using formula for Paging.

Here is the third part of the query:

select UserID, Username, Address, DivisionID, DivisionName
from #temp
where UniqueID between ( @PageNo - 1 ) * @PageSize + 1
and @PageNo * @PageSize

Notice the formula used above.
The formula used to get the data between the wanted page is:

select data where UniqueID between ( @PageNo - 1 ) * @PageSize + 1 and @PageNo * @PageSize

Therefore, if we would like to retrieve the data of PageSize = 5 on Page 1:
We will get the data with the following condition

UniqueID between ( 1 - 1 ) * 5 + 1 and 1 * 5
UniqueID between 1 and 5

If we would like to retrieve the data of PageSize = 5 on Page 2:
We will get the data with the following condition

UniqueID between ( 2 - 1 ) * 5 + 1 and 2 * 5
UniqueID between 6 and 10

Example:
The data retrived from the select query will be:


If the PageSize is set to 5 item per page, then the top 5 users will be displayed on Page 1 and the next 5 users will be displayed on Page 2.
Using the query

-- Example : PageSize = 5, PageNo = 1
spr_GetSimplePaging 5, 1

The data retrieved on Page 1 will be:



To get the data for the second page, use the query:

-- Example : PageSize = 5, PageNo = 2
spr_GetSimplePaging 5, 2

The data retrieved on Page 2 will be:



The sample query used to create simple paging above will perform quite slowly when the data is very big. To optimize the performance, therefore, we will have to make a little modification on the query.

Here is the modified query:

create procedure dbo.spr_GetModifiedPaging
@PageSize int, -- @PageSize = item per page
@PageNo int -- @PageNo = current page
as
set nocount on

-- Create Temporary Table to Store the Raw Data
-- The content of the temporary table: An identity field which will be used to determine the paging
--    and the UNIQUE COLUMN(S) - usually marked as primary key - contained in the original table
create table #temp
(  UniqueID int identity, -- will be used to determine the paging
   UserID char(20)
)

-- Select the Raw Data into temporary table #temp
insert into #temp ( UserID )
select a.UserID
from dbo.MsUser a
order by a.Username

-- Get Data Using Formula for Paging
-- Join the data with the original table(s)
select a.UserID, b.Username, b.Address, b.DivisionID, c.DivisionName
from #temp a
left join dbo.MsUser b
    on b.UserID = a.UserID
left join dbo.MsDivision c
    on c.DivisionID = b.DivisionID
where UniqueID between ( @PageNo - 1 ) * @PageSize + 1
and @PageNo * @PageSize

-- Drop temporary table
drop table #temp

Instead of storing all raw data in the temporary table, we actually only need to store the unique data, thus, we only need to create a temporary table containing UniqueID and the UserID:

create table #temp
(  UniqueID int identity, -- will be used to determine the paging
   UserID char(20)
)

On the second part, the query will be much simpler:

insert into #temp ( UserID )
select a.UserID
from dbo.MsUser a
order by a.Username

On the third part, we will then have to join the tables used to get the complete data to be retrieved.

select a.UserID, b.Username, b.Address, b.DivisionID, c.DivisionName
from #temp a
left join dbo.MsUser b
    on b.UserID = a.UserID
left join dbo.MsDivision c
    on c.DivisionID = b.DivisionID
where UniqueID between ( @PageNo - 1 ) * @PageSize + 1
and @PageNo * @PageSize

Using the modified query, the temporary table created will be smaller. Consequently, the raw data stored in the temporary table will be smaller. The complete data will then be selected by joining it with the original table(s) on the last part. This enables better performance while retrieving a very big data.


If you need the source code, click on the link to the file to download the source code: Paging in SQL