CODEWITHSUNDEEP
sqlsql-serversql-server-2012
shane shane
shane shane

Reputation: 1

Better SQL pagination technique

Is there a better way to do paging in SQL server?

For example, I have to add @skip to @take using the following technique:

;WITH tmp_cte AS (
 SELECT ROW_NUMBER() OVER (ORDER BY LastName) AS RowNumber,
        LastName, 
        FirstName
   FROM person.Person
  WHERE FirstName like '%ad%'
)
SELECT * 
FROM tmp_cte 
WHERE RowNumber > @skip --10 
AND RowNumber <= @Take--20

is there a better way so that I don't have to add skip to take?

Upvotes: 0

Views: 131

Answers (2)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

A better solution is possible if there is an unique index on Person.Person table (ex. there is a unique index on PersonID column and this index is clustered - for example if you have a clustered PK). In such cases above query can be rewritten thus:

;WITH tmp_cte AS (
    SELECT ROW_NUMBER() OVER (ORDER BY LastName) AS RowNumber, PersonID
    FROM person.Person
    WHERE FirstName like '%ad%'
)
SELECT ...
FROM Person.Person p
WHERE p.PersonID IN -- PersonID is the key of this UNIQUE INDEX
(
SELECT PersonID FROM tmp_cte 
WHERE RowNumber > @skip 10 AND RowNumber <= @Take--20
)

or 

DECLARE @Rows TABLE (ID INT PRIMARY KEY);
;WITH tmp_cte AS (
    SELECT ROW_NUMBER() OVER (ORDER BY LastName) AS RowNumber, PersonID
    FROM person.Person
    WHERE FirstName like '%ad%'
)
INSERT @Rows (ID)
SELECT PersonID FROM tmp_cte 
WHERE RowNumber > @skip 10 AND RowNumber <= @Take--20

SELECT ...
FROM Person.Person p
WHERE p.PersonID IN (SELECT ID FROM @Rows); -- PersonID is the key of this UNIQUE INDEX

Note #1: In this particular case, an index on (FirstName, LastName) or (LastName, FirstName) could be also useful.

Note #2: This solution should perform better if you have more columns within final SELECT clause.

Upvotes: 0

Harminder
Harminder

Reputation: 2229

In SQL 2012 it’s very simple:

SELECT LastName, FirstName
  FROM person.Person
 WHERE FirstName like '%ad%'
 ORDER BY LastName
 OFFSET 10 ROWS 
 FETCH NEXT 10 ROWS ONLY;

Check out this link for other versions of SQL servers + performance comparison.

Upvotes: 6

Related Questions