How to do pagination in SQL Server 2008

Question

How do you do pagination in SQL Server 2008 ?

There's no such thing as MSSQL 2008. You meant, "SQL Server 2008" — John Saunders, Feb 11 '10 at 12:41

score 48 · Answer 1 · edited Sep 15 '15 at 19:15

48

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Example:

WITH CTEResults AS
(
    SELECT IDColumn, SomeField, DateField, ROW_NUMBER() OVER (ORDER BY DateField) AS RowNum
    FROM MyTable
)

SELECT * 
FROM CTEResults
WHERE RowNum BETWEEN 10 AND 20;

edited Sep 15 '15 at 19:15

izstas

5,004
3
42
56

answered Feb 11 '10 at 12:27

AdaTheDev

142,592
28
206
200

score 45 · Accepted Answer · answered Feb 11 '10 at 12:28

45

You can try something like

DECLARE @Table TABLE(
        Val VARCHAR(50)
)

DECLARE @PageSize INT,
        @Page INT

SELECT  @PageSize = 10,
        @Page = 2

;WITH PageNumbers AS(
        SELECT Val,
                ROW_NUMBER() OVER(ORDER BY Val) ID
        FROM    @Table
)
SELECT  *
FROM    PageNumbers
WHERE   ID  BETWEEN ((@Page - 1) * @PageSize + 1)
        AND (@Page * @PageSize)

answered Feb 11 '10 at 12:28

Adriaan Stander

162,879
31
289
284

I think this code dosn't work. Did you test this code ? (it should replace the "PageNumbers" table with the "@Table". – RockOnGom Mar 20 '13 at 02:21
2

@zeitgeist, it is making use of a CTE called PageNumbers. Did you test the code as is? – Adriaan Stander Mar 20 '13 at 04:35
1

This paging algorithm is wrong. With pages 1 and 2 it works, but starting with page 3, it will start skipping entries. page 2 will go from `11` to `20` but page 3 will go from `22` to `30` skipping entry `21`. – João Eduardo Feb 14 '20 at 20:38

score 15 · Answer 3 · answered Jul 12 '13 at 15:29

SQL Server 2012 provides pagination functionality (see http://www.codeproject.com/Articles/442503/New-features-for-database-developers-in-SQL-Server)

In SQL2008 you can do it this way:

declare @rowsPerPage as bigint; 
declare @pageNum as bigint; 
set @rowsPerPage=25; 
set @pageNum=10;   

With SQLPaging As   ( 
    Select Top(@rowsPerPage * @pageNum) ROW_NUMBER() OVER (ORDER BY ID asc) 
    as resultNum, * 
    FROM Employee )
select * from SQLPaging with (nolock) where resultNum > ((@pageNum - 1) * @rowsPerPage)

Prooven! It works and scales consistently.

result can be different if you get rid of top. This method does't not guarantee performance the same result if you remove top, unless table have been sorted by id. — plasma, Mar 16 '18 at 19:20

score 2 · Answer 4 · edited Jan 30 '13 at 06:46

2

1) CREATE DUMMY DATA

CREATE TABLE #employee (EMPID INT IDENTITY, NAME VARCHAR(20))

DECLARE @id INT = 1

WHILE @id < 200

BEGIN
INSERT INTO #employee ( NAME ) VALUES ('employee_' + CAST(@id AS VARCHAR) )
SET @id = @id + 1
END

2) NOW APPLY THE SOLUTION.

This case assumes EMPID to be unique and sorted column.

Off-course, you will apply it a different column...

DECLARE @pageSize INT = 20

SELECT * FROM (

SELECT *, PageNumber =  CEILING(CAST(EMPID AS FLOAT)/@pageSize)   
FROM #employee
) MyQuery

WHERE MyQuery.PageNumber = 1

edited Jan 30 '13 at 06:46

Prahalad Gaggar

11,389
16
53
71

answered Jan 30 '13 at 06:22

RehmanAfridi

21
2

1

Lovely approch but adding a new column might be an overhead. – Ankit Suhail Feb 17 '14 at 20:26

score 0 · Answer 5 · answered Oct 19 '16 at 22:37

These are my solution for paging the result of query in SQL server side. I have added the concept of filtering and order by with one column. It is very efficient when you are paging and filtering and ordering in your Gridview.

Before testing, you have to create one sample table and insert some row in this table : (In real world you have to change Where clause considering your table field and maybe you have some join and subquery in main part of select)

Create Table VLT
(
    ID int IDentity(1,1),
    Name nvarchar(50),
    Tel Varchar(20)
)
GO


Insert INTO VLT
VALUES
    ('NAME' + Convert(varchar(10),@@identity),'FAMIL' +     Convert(varchar(10),@@identity))
GO 500000

In SQL server 2008, you can use the CTE concept. Because of that, I have written two type of query for SQL server 2008+

-- SQL Server 2008+

DECLARE @PageNumber Int = 1200
DECLARE @PageSize INT = 200
DECLARE @SortByField int = 1 --The field used for sort by
DECLARE @SortOrder nvarchar(255) = 'ASC' --ASC or DESC
DECLARE @FilterType nvarchar(255) = 'None' --The filter type, as defined on the client side (None/Contain/NotContain/Match/NotMatch/True/False/)
DECLARE @FilterValue nvarchar(255) = '' --The value the user gave for the filter
DECLARE @FilterColumn int = 1 --The column to wich the filter is applied, represents the column number like when we send the information.

SELECT 
  Data.ID,
  Data.Name,
  Data.Tel
FROM
  (  
    SELECT 
      ROW_NUMBER() 
        OVER( ORDER BY 
                CASE WHEN @SortByField = 1 AND @SortOrder = 'ASC'
                      THEN VLT.ID END ASC,
                CASE WHEN @SortByField = 1 AND @SortOrder = 'DESC'
                      THEN VLT.ID END DESC,
                CASE WHEN @SortByField = 2 AND @SortOrder = 'ASC'
                      THEN VLT.Name END ASC,
                CASE WHEN @SortByField = 2 AND @SortOrder = 'DESC'
                      THEN VLT.Name END ASC,
                CASE WHEN @SortByField = 3 AND @SortOrder = 'ASC'
                      THEN VLT.Tel END ASC,
                CASE WHEN @SortByField = 3 AND @SortOrder = 'DESC'
                      THEN VLT.Tel END ASC
         ) AS RowNum
      ,*  
    FROM VLT 
    WHERE
      ( -- We apply the filter logic here
        CASE
          WHEN @FilterType = 'None' THEN 1

          -- Name column filter
          WHEN @FilterType = 'Contain' AND @FilterColumn = 1
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.ID LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 1
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.ID NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 1
            AND VLT.ID = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 1
            AND VLT.ID <> @FilterValue THEN 1               

          -- Name column filter
          WHEN @FilterType = 'Contain' AND @FilterColumn = 2
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Name LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 2
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Name NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 2
            AND VLT.Name = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 2
            AND VLT.Name <> @FilterValue THEN 1         

         -- Tel column filter   
         WHEN @FilterType = 'Contain' AND @FilterColumn = 3
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Tel LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 3
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Tel NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 3
            AND VLT.Tel = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 3
            AND VLT.Tel <> @FilterValue THEN 1    

        END
      ) = 1   
  ) AS Data
WHERE Data.RowNum > @PageSize * (@PageNumber - 1)
  AND Data.RowNum <= @PageSize * @PageNumber
ORDER BY Data.RowNum

GO

And second solution with CTE in SQL server 2008+

DECLARE @PageNumber Int = 1200
DECLARE @PageSize INT = 200
DECLARE @SortByField int = 1 --The field used for sort by
DECLARE @SortOrder nvarchar(255) = 'ASC' --ASC or DESC
DECLARE @FilterType nvarchar(255) = 'None' --The filter type, as defined on the client side (None/Contain/NotContain/Match/NotMatch/True/False/)
DECLARE @FilterValue nvarchar(255) = '' --The value the user gave for the filter
DECLARE @FilterColumn int = 1 --The column to wich the filter is applied, represents the column number like when we send the information.

;WITH
  Data_CTE
  AS
  (  
    SELECT 
      ROW_NUMBER() 
        OVER( ORDER BY 
                CASE WHEN @SortByField = 1 AND @SortOrder = 'ASC'
                      THEN VLT.ID END ASC,
                CASE WHEN @SortByField = 1 AND @SortOrder = 'DESC'
                      THEN VLT.ID END DESC,
                CASE WHEN @SortByField = 2 AND @SortOrder = 'ASC'
                      THEN VLT.Name END ASC,
                CASE WHEN @SortByField = 2 AND @SortOrder = 'DESC'
                      THEN VLT.Name END ASC,
                CASE WHEN @SortByField = 3 AND @SortOrder = 'ASC'
                      THEN VLT.Tel END ASC,
                CASE WHEN @SortByField = 3 AND @SortOrder = 'DESC'
                      THEN VLT.Tel END ASC
         ) AS RowNum
      ,*  
    FROM VLT
    WHERE
      ( -- We apply the filter logic here
        CASE
          WHEN @FilterType = 'None' THEN 1

          -- Name column filter
          WHEN @FilterType = 'Contain' AND @FilterColumn = 1
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.ID LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 1
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.ID NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 1
            AND VLT.ID = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 1
            AND VLT.ID <> @FilterValue THEN 1               

          -- Name column filter
          WHEN @FilterType = 'Contain' AND @FilterColumn = 2
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Name LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 2
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Name NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 2
            AND VLT.Name = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 2
            AND VLT.Name <> @FilterValue THEN 1         

         -- Tel column filter   
         WHEN @FilterType = 'Contain' AND @FilterColumn = 3
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Tel LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'NotContain' AND @FilterColumn = 3
            AND ( -- In this case, when the filter value is empty, we want to show everything.
                VLT.Tel NOT LIKE '%' + @FilterValue + '%'
               OR
                @FilterValue = ''
               ) THEN 1
          WHEN @FilterType = 'Match' AND @FilterColumn = 3
            AND VLT.Tel = @FilterValue THEN 1
          WHEN @FilterType = 'NotMatch' AND @FilterColumn = 3
            AND VLT.Tel <> @FilterValue THEN 1    

        END
      ) = 1     
  )

SELECT 
  Data.ID,
  Data.Name,
  Data.Tel
FROM Data_CTE AS Data
WHERE Data.RowNum > @PageSize * (@PageNumber - 1)
  AND Data.RowNum <= @PageSize * @PageNumber
ORDER BY Data.RowNum

score 0 · Answer 6 · answered Apr 06 '18 at 08:54

0

Another solution which works from SQL 2005 at least, is to use TOP with SELECT subqueries and ORDER BY clauses.

In brief, retrieving page 2 rows with 10 rows per page is the same as retrieving the last 10 rows of the first 20 rows. Which translates into retrieving the first 20 rows with ASC order, and then the first 10 rows with DESC order, before ordering again using ASC.

Example : Retrieving page 2 rows with 3 rows per page

create table test(id integer);
insert into test values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

select * 
    from (
        select top 2 * 
            from (
                select  top (4) * 
                    from test 
                    order by id asc) tmp1
            order by id desc) tmp1 
    order by id asc

answered Apr 06 '18 at 08:54

Nicolas Riousset

3,447
1
22
25

TOP solves the problem for the first page, but not for the nexts... I need to request data in pages with 50 records. First page i use select TOP 50, ok, but what about the second page ? – delphirules Oct 16 '18 at 18:18
That's what my sample is showing. To get the 50 records of page 2 : select the top 100 records, then order them by reverse order and select the top 50 records. You now have your page 2 records, but in reverse order. Reorder them properly and you're done. But that's complicated code, you should use it only if you.re stuck with an old SQL Server version, and don't have access to features such as ROW_NUMBER(). – Nicolas Riousset Oct 17 '18 at 19:19

score 0 · Answer 7 · answered Oct 26 '20 at 11:30

The query that i have used for pagination is this (on Oracle DB).

SELECT * FROM tableName
WHERE  RowNum >= 1
AND RowNum < 20
ORDER BY RowNum;

RowNum- it is variable provide by DB .For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows.

score -3 · Answer 8 · edited Dec 10 '19 at 06:57

SELECT DISTINCT Id,ParticipantId,ActivityDate,IsApproved,
    IsDeclined,IsDeleted,SubmissionDate,    IsResubmitted,  

    [CategoryId] Id,[CategoryName] Name,

    [ActivityId] [Id],[ActivityName] Name,Points,   

    [UserId] [Id],Email,
    ROW_NUMBER() OVER(ORDER BY Id desc)   AS RowNum from
    (SELECT DISTINCT
    Id,ParticipantId,
    ActivityDate,IsApproved,
    IsDeclined,IsDeleted,
    SubmissionDate, IsResubmitted,  

    [CategoryId] [CategoryId],[CategoryName] [CategoryName],

    [ActivityId] [ActivityId],[ActivityName] [ActivityName],Points, 

    [UserId] [UserId],Email,
    ROW_NUMBER() OVER(ORDER BY Id desc)   AS RowNum from

     (SELECT DISTINCT ASN.Id,
    ASN.ParticipantId,ASN.ActivityDate,
    ASN.IsApproved,ASN.IsDeclined,
    ASN.IsDeleted,ASN.SubmissionDate,
    CASE WHEN (SELECT COUNT(*) FROM FDS_ActivitySubmission WHERE ParentId=ASN.Id)>0 THEN CONVERT(BIT, 1) ELSE CONVERT(BIT, 0) END IsResubmitted,

    AC.Id [CategoryId], AC.Name [CategoryName],

    A.Id [ActivityId],A.Name [ActivityName],A.Points,

    U.Id[UserId],U.Email    


FROM
FDS_ActivitySubmission ASN WITH (NOLOCK)
INNER JOIN  
    FDS_ActivityCategory AC WITH (NOLOCK)
ON 
    AC.Id=ASN.ActivityCategoryId
        INNER JOIN
    FDS_ApproverDetails FDSA
ON
FDSA.ParticipantID=ASN.ParticipantID

        INNER JOIN
       FDS_ActivityJobRole FAJ
ON
     FAJ.RoleId=FDSA.JobRoleId
    INNER JOIN

    FDS_Activity A WITH (NOLOCK)
ON 
    A.Id=ASN.ActivityId
INNER JOIN
   Users U WITH (NOLOCK)
ON
    ASN.ParticipantId=FDSA.ParticipantID
WHERE
       IsDeclined=@IsDeclined AND IsApproved=@IsApproved    AND ASN.IsDeleted=0
       AND
       ISNULL(U.Id,0)=ISNULL(@ApproverId,0)
       AND ISNULL(ASN.IsDeleted,0)<>1)P)t where t.RowNum between 
       (((@PageNumber - 1) * @PageSize) + 1) AND (@PageNumber * PageSize)
    AND t.IsDeclined=@IsDeclined AND t.IsApproved=@IsApproved AND t.IsDeleted = 0
 AND (ISNULL(t.Id,0)=ISNULL(@SubmissionId,0)or ISNULL(@SubmissionId,0)<=0)

Please do not only post some code, but explain *why* and *how* this code solves the issue. — Paul Kertscher, Jan 30 '17 at 08:03

How to do pagination in SQL Server 2008

8 Answers8

Linked

Related