I have an Employee
table that has one million records. I have following SQL for paging data in a web application. It is working fine. However what I see as an issue is - the derived table tblEmployee
selects all records in the Employee
table (to create the MyRowNumber
values).
I think, this causes selection of all records in the Employee
table.
Does it really work so? Or is SQL Server optimized to select only the 5 records from the original Employee
table too?
DECLARE @Index INT;
DECLARE @PageSize INT;
SET @Index = 3;
SET @PageSize = 5;
SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY EmpID asc) as MyRowNumber,*
FROM Employee) tblEmployee
WHERE MyRowNumber BETWEEN ( ((@Index - 1) * @PageSize )+ 1) AND @Index*@PageSize