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.