Efficient sql server paging

Question

Due to the first two comments I've removed all my own code and placed the example directly from 4 guys here.

I'm interested in how the 'select @first_id' should be coded. The example shows the rows being pulled using joins and I would expect that the first_id wouldn't be a valid place to start because it doesn't use the same join syntax.

CREATE  PROCEDURE [dbo].[usp_PageResults_NAI] 
(
    @startRowIndex int,
    @maximumRows int
)
AS

DECLARE @first_id int, @startRow int

-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first employeeID for our page of records
SET ROWCOUNT @startRowIndex
SELECT @first_id = employeeID FROM employees ORDER BY employeeid

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

SELECT e.*, d.name as DepartmentName 
FROM employees e
   INNER JOIN Departments D ON
       e.DepartmentID = d.DepartmentID
WHERE employeeid >= @first_id
ORDER BY e.EmployeeID

SET ROWCOUNT 0

GO

The example does seem OK because an Employee is in exactly one department so the join won't change the number of rows. This is a SQL Server 2000 approach though. `row_number` is easier in 2005+ — Martin Smith, Oct 10 '11 at 20:53

score 2 · Accepted Answer · answered Oct 10 '11 at 20:48

2

You can to efficient paging using ROW_NUMBER()

DECLARE @skipRows Int = 10 --Change to input parameter to sp
DECLARE @takeRows Int = 20 --Change to input parameter to sp

SELECT *
FROM (
    SELECT 
         ROW_NUMBER() OVER (ORDER BY a.DateCreated) As RowNumber, 
         a.PKID, 
         a.AlertUrl, 
         a.AlertDescription, 
         a.Users_PKID_creator, 
         dbo.Users_GetFullName(a.Users_PKID_creator) as Users_FullName,
         a.Dealers_PKID, 
         d.Dealer,
         dbo.convertDateFromUTC(a.DateCreated, @dealers_pkid) as DateCreated,
         dbo.convertDateFromUTC(a.DateCreated, @dealers_pkid) as ComparisonDate,
         dbo.convertDateFromUTC(a.DateModified, @dealers_pkid) as DateModified,
         a.Active,
         a.Contacts_PKID,
         dbo.Contacts_GetFullName(a.Contacts_PKID) as Contacts_FullName
from     Alerts a
join     Dealers d on d.PKID = a.Dealers_PKID
where    a.DateCreated between dbo.convertDateToUTC(@datetimeDateStart, @dealers_pkid) and dbo.convertDateToUTC(@datetimeDateEnd, @dealers_pkid)
and      a.Active = @bitActive
and      a.PKID >= @first_id
    ) AS [t1]
WHERE [t1].RowNumber BETWEEN @skipRows + 1 AND @skipRows + @takeRows

answered Oct 10 '11 at 20:48

Magnus

45,362
8
80
118

2

Thanks Magnus. This is the way I've always done it but recently read that it doesn't work well for large data sets. What do you think? – Keith Myers Oct 10 '11 at 20:52
Where have you read that? To my knowledge this is the best way to do it. – Magnus Oct 10 '11 at 20:55
From what I understand that article refers to SQL Server 2000 – Magnus Oct 10 '11 at 20:59
I think Martin Smith just commented and proved you correct Magnus. – Keith Myers Oct 10 '11 at 21:00
@KeithMyers - The code is 2000 compatible so I thought it was an old article. The point it makes is still true though. `row_number` can be quite inefficient as sometimes it would be optimal to use a different plan to get to the start of the results from the operators that actually retrieve the results but `row_number` doesn't do this. [See for example this answer](http://stackoverflow.com/questions/5441839/sql-query-how-can-i-get-data-of-row-with-number-1000-direclty/5442376#5442376) – Martin Smith Oct 10 '11 at 21:04

Efficient sql server paging

1 Answers1