best t-sql for providing nth page in showing data?

Question

I want to item 1 to 50 in my first page by using

SELECT TOP(50) * 
FROM Items_table 

and it works fine but how can I get second 50 items for next page? How should be the query be?

Please note that I can't use LIMIT because I am using SQL Server.

I have also problem with queries like:

select * 
from (
    select 
        ROW_NUMBER() over (
            ORDER BY date_of_creation desc, time_of_creation desc) AS Row2,
        * 
    from 
        Items_table) ir
where 
    ir.Row2 between @start and @end

The problem is in this condition table adapter doesn't support OVER.

Is there any other t-sql code that table adapter support?

Answer 1

I've achieved that with this query in table adapters:

select top (@count) * 
from Items_table
where id not in (select top(@count2) id from Items_table)
order by Date_Of_Creation desc,Time_Of_Creation desc

Thanks for participating.

Update: Please don't use that either with ordering right in inner query because I had repeated results and some bugs. In addition when you want load 10th page the performance of query will be bad (The time needed to execute query is not acceptable).

Instead I was forced to use this method (and it is good also):

Select TOP (@count) from Item_table
order by Date_Of_Creation desc,Time_Of_Creation desc

Then each page should contain some of the rows of this query(If you are going to use multi page).

If you have one page that when user reaches bottom you want to load more, in this method you should execute this query every time you want to load more with bigger @count and load the items in the page.