SQL 2005 Huge table data paging with filter

I have a table in sql 2005 with a big count of data - smth like 1 500 000 rows right now and later it should be more. Before paging I need to detect what rows the user can read (sql query for checking is a heavy which refer to several other tables) and the result should be paged.

What the best practice to work开发者_高级运维 with the huge table that should be filtered and paged after all?

Thanks in advance!

If you want to return paginated results in SQL Server your best bet is probably to use the ROW_NUMBER() function. Here is an example that would get you the 400th-410th results:

SELECT  ID, Name, Date
FROM     (SELECT TOP 410 ROW_NUMBER() OVER (ORDER BY id)
             AS Row, ID, Name, Date FROM MyTable)
            AS MyPagedTable
WHERE  Row >= 400 AND Row <= 410

Make sure you have the proper indexes in place. If you are getting performance issues then I would recommend looking at the execution plan and seeing where the problem areas are.