Use ROW_NUMBER in SQL to retrive Paging specific records from table.

In SQL Server,

ROW_NUMBER() function returns number of row from result set(you have requested thru query).

 

Following sample query will result in only 10 records from Product table.

WITH DummyTable AS (
SELECT
ROW_NUMBER() OVER(order by ProductName ASC ) as RowNum,
ProductId,ProductName
from ProductMaster
)
select * from DummyTable
where rownum>=210 AND rownum<220

 

Such query is useful if you want to implement paging in your web application/project and  you want to retrieve only records from table according to your current page.

Advertisements