How to create t-sql to load next n-amount of records?

Question

I need an example of creating t-sql query to load next 10 records (depends on the default row amount in grid).

the same kind of linq has to skip rows.

So for example I have 100K of results I need to load just 10 between 100 and 110 records and so on. The idea is to make it page load very fast

I need also to build paging for my grid so I need to know how many records in total

Answer 1

In MS SQL 2005/2008 you can do something like this

with cte
as
(
    select row_number() over (order by ID) RowNumber, *
    from MyTable
) 
select *
from cte
where RowNumber between 10 and 20

[Edit] With total count column

select *
from
(
    select
        row_number() over (order by ID) RowNumber, 
        count(*) over() TotalRowCount,
        *
    from MyTable
) tt
where RowNumber between 10 and 20

Answer 2

Try this

SELECT YourColumn1, YourColumn2, RN
FROM
(
   SELECT YourTable1.*, ROW_NUMBER() OVER (ORDER BY YourTable1PK) RN
   FROM YourTable1
) sq
 WHERE sq.rn BETWEEN 10 AND 20

Answer 3

You can use a query like this. It should be fast as long as you have an index on the Records.Id column.

select *
from 
    (select
        row_number() over (order by Id) as [RowNum],
        count(*) over() as [TotalCount],
        Id from @Records) as R
where
    [RowNum] between @StartRow and (@StartRow + @PageSize)

Check out MSDN to find out more about the ROW_NUMBER() function.