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
I think, this causes selection of all records in the
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