Pagination in SQL 2012/2008/2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Pagination in SQL 2012/2008/2005

Author

Topic

steppinthrax
Starting Member

27 Posts

Posted - 2015-04-16 : 21:39:21

I'm posed with a potential question on how you can implement "pagination" from the sql end. Example, you have two inputs.

1. Rows per page
2. Next page.

So instead of selecting all data from the table you specify or pre-set the row limit then you need to be able to move to the next 50 rows. My first thought is to make the use of rownum and do some calculation based on the "pagesize".

steppinthrax
Starting Member

27 Posts

Posted - 2015-04-17 : 00:17:13

OK I think I got it but need some more help from you guys.

Select * from TABLE
Limit 50 Offset 50 <---(0 = 1st page, 50 = 2nd page, 100 = 3rd page)

I think I partly found the way to do this without using Limit/Offset

select * from TABLE
where rownum >49 and rownum <101 (Only issue with this is I have to do the calculation for the range???)

Kristen
Test

22859 Posts

Posted - 2015-04-17 : 07:32:31

I don't use SQL 2012, but for earlier versions LIMIT, OFFSET and ROWNUM don't exist (they are found in Oracle and MySQL I believe).

For MS SQL I would use ROW_NUMBER(), PARTITION and ORDER BY to "arrange" the items into a defined order and then select only WHERE the Row Number is between 50 and 100.

One thing that we bump up against with this is "Should we pass the Page Number" as a parameter or should we past the Start/End point?

If you are on Page 50 and someone inserts, or deletes, and item from the underlying table then users either skip one or see one twice. This covers things that go out of stock, or posts on a forum which are displayed in "most recent posting date" order

OTOH if you say "Next 50 AFTER xxx" or "Previous 50 BEFORE yyy" then the next page is somewhat easier.


SELECT TOP 50 ...
FROM MyTable
WHERE PKey > 'xxx'
ORDER BY PKey ASC


SELECT *
FROM
(
SELECT TOP 50 ...
FROM MyTable
WHERE PKey < 'YYY'
ORDER BY PKey DESC
) AS X
ORDER BY PKey ASC

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-17 : 08:53:23

Here some examples.

CREATE TABLE #TestPaging
(RowID Int Identity (1,1),OddRow bit, SomeText varchar(50))

;With LoadTable
AS
(
SELECT 1+ 0 Rw, CASE WHEN 1%2 >0 THEN 1 ELSE 0 END OddRow, 'Blah Blah' + CAST(1 as VARCHAR(10)) SomeText
UNION ALL
SELECT Rw + 1 , CASE WHEN (Rw + 1)%2 >0 THEN 1 ELSE 0 END OddRow, 'Blah Blah' + CAST(Rw+1 as VARCHAR(10))
FROM LoadTable
WHERE Rw + 1 < 101
)

INSERT INTO #TestPaging(OddRow, SomeText)
SELECT OddRow, SomeText FROM LoadTable

CREATE PROC #PagingExample2012
@Position int = 0, -- where in the set we wish to page from
@Fetch int = 25 -- fetch next how many in the set
AS
BEGIN
SELECT *,COUNT(*) OVER() TotalRowCount FROM #TestPaging
ORDER BY 1
OFFSET @Position ROWS
FETCH NEXT @Fetch ROWS ONLY;
END

CREATE PROC #PagingExample2005And2008
@Position int = 0, -- where in the set we wish to page from
@Fetch int = 25 -- fetch next how many in the set
AS
BEGIN

WiTH MyPaging
AS
(
SELECT ROW_NUMBER() OVER( ORDER BY RowID) RW,*,COUNT(*) OVER() TotalRowCount FROM #TestPaging

)

SELECT * FROM MyPaging
WHERE Rw BETWEEN @Position AND @Position + @Fetch

END

EXEC #PagingExample2012 @Position =0 -- will fetch first 25 or 25

EXEC #PagingExample2012 @Position = 25-- will fetch second 25 or 25

EXEC #PagingExample2012 @Position = 50 -- etc

EXEC #PagingExample2012 @Position = 75

--
EXEC #PagingExample2005And2008 @Position =0

EXEC #PagingExample2005And2008 @Position = 25

EXEC #PagingExample2005And2008 @Position = 50

EXEC #PagingExample2005And2008 @Position = 75

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources