Paging in SQL 2

Regarding my post about Paging in SQL, I have found another easier way to include a column as a running number of the records. Hence, we do not need to create a temporary table, and the code will be much simpler.

I will use the same tables for example. In case you find difficulty finding the post about Paging in SQL, I include them here.

Here is the Create table Query:

CREATE TABLE dbo.MsUser
(  UserID CHAR(20) PRIMARY KEY,
   Username VARCHAR(100),
   Address VARCHAR(50),
   DivisionID INT
)

And here is the query to insert sample data:

-- 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 is the data inserted to the tables:

Here is the query to get the data using paging:

DECLARE @PageSize INT, @PageNo INT
SELECT a.Username FROM (
    SELECT Username, RowNumber = ROW_NUMBER() OVER (ORDER BY Username) 
    FROM MsUser
) a WHERE a.RowNumber BETWEEN (@PageNo - 1 )* @PageSize + 1 AND @PageNo * @PageSize

Note that the key is in this part of code:

ROW_NUMBER() OVER (ORDER BY Username)

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.

e.g. 1

PageSize = 5, PageNo = 1

DECLARE @PageSize INT, @PageNo INT
SELECT  @PageSize = 5, @PageNo = 1
SELECT a.Username FROM (
    SELECT Username, RowNumber = ROW_NUMBER() OVER (ORDER BY Username) 
    FROM MsUser
) a WHERE a.RowNumber BETWEEN (@PageNo - 1 )* @PageSize + 1 AND @PageNo * @PageSize

The data retrieved on Page 1 will be:

e.g. 2

PageSize = 5, PageNo = 2

DECLARE @PageSize INT, @PageNo INT
SELECT  @PageSize = 5, @PageNo = 2
SELECT a.Username FROM (
    SELECT Username, RowNumber = ROW_NUMBER() OVER (ORDER BY Username) 
    FROM MsUser
) a WHERE a.RowNumber BETWEEN (@PageNo - 1 )* @PageSize + 1 AND @PageNo * @PageSize

And the data retrieved on Page 2 will be: