Efficient way to publish the page from this sql server article

Question

I read about how effectively flipping large amounts of data, because I was not happy with that Row_Number, and Fetchworst of all.

This article is: http://www.4guysfromrolla.com/webtech/042606-1.shtml

Now this article contains this piece of code:

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 

This demo code looks fine (like the other demos you see :)). The above code only works because it uses Order By employeeidin SELECT @first_id = employeeID FROM employees ORDER BY employeeid.

, FirstName . ? , , , WHERE employeeid >= @first_id, first_id, . , where order by.

:

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

, , .

, - ? .

Answer 1

, , , ROW_NUMBER . , , , , , ?

, (-) , , .

, ROW_NUMBER , , , :

CREATE PROCEDURE dbo.usp_PagedResults_RowNumber
(
   @startRowIndex int,
   @maximumRows int
)
AS
WITH Emp AS
(   SELECT e.*, rn = ROW_NUMBER() OVER(ORDER BY e.EmployeeID)
    FROM employees e
)
SELECT  TOP (@MaximumRows) 
        EmployeeID, 
        LastName, 
        FirstName, 
        e.DepartmentID, 
        Salary,
        HireDate, 
        d.Name AS DepartmentName
FROM    Emp e
        INNER JOIN Departments D ON
           e.DepartmentID = d.DepartmentID
WHERE   rn >= @startRowIndex
ORDER BY EmployeeID;

:

EXECUTE usp_PageResults_NAI 4500, 20;
EXECUTE usp_PagedResults_RowNumber 4500, 20;

, IO, , , :

Table 'Employees'. Scan count 1, logical reads 48
(1 row(s) affected)

(20 row(s) affected)
Table 'Departments'. Scan count 1, logical reads 41
Table 'Employees'. Scan count 1, logical reads 2

(1 row(s) affected)

physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0., .

ROW_NUMBER():

(20 row(s) affected)
Table 'Departments'. Scan count 1, logical reads 41
Table 'Employees'. Scan count 1, logical reads 48

(1 row(s) affected)

, ROW_NUMBER 2 .

. Rowcount 84% :

ROW_NUMBER - 16%.

, , . , IO ROW_NUMBER.

, ROWCOUNT FirstName, FirstName, ROW_NUMBER . .

CREATE NONCLUSTERED INDEX IX_Employees_FirstName ON dbo.Employees (FirstName ASC) INCLUDE (DepartmentID);

, :

SELECT  EmployeeID, 
        DepartmentID, 
        RowNumber = ROW_NUMBER() OVER(ORDER  BY FirstName, EmployeeID)
FROM    Employees;

:

Table 'Employees'. Scan count 1, logical reads 501

Table 'Employees'. Scan count 1, logical reads 249

, , , .

Answer 2

SP, . SP , , , , .., .

- , , , .., - , , , ( btw , ).

.NET- Generic List (of integer). , - (, 50 ) SP. SP, , , .. INNER JOIN .

, , , . , " ", .