Paging of Large Datasets in Sql Server

When you are fetching large "datasets" using ROW_NUMBER(), you might experience a long

delays in getting result and sometime it timeout expired, To overcome this situation you 

need a better and efficient approach, Fortunately there is an efficient solution for that

teasy situation but question is what .

"Table Variables" are light weight because they does not allow explicit addition of indexes 

after it's declaration only implicit indexes can be created using primary key or unique key 

and also scope of the table variable is the Batch or Stored Procedure in which it is declared. 

And they can’t be dropped explicitly, they are dropped automatically when batch execution completes or the Stored Procedure execution completes. 

So in a situation like this it can be useful in a way that create a table variable and insert
fetched data in it along with an auto incremented id then fetch the records from that
temp table with paging filter applied then you would get you expected result.

Here is a small demonstration :

CREATE PROCEDURE GetEmployee

       @PageSize BIGINT = 10,

       @PageNo BIGINT = 1

AS

BEGIN

       SET QUOTED_IDENTIFIER OFF

       DECLARE @TempItems TABLE (

               Rowid BIGINT IDENTITY

              ,EmployeeID BIGINT

              ,EmployeeName VARCHAR(155)

              )

       DECLARE @maxRow BIGINT    

       SET @maxRow = (@PageNo * @PageSize) + @PageSize + 1

       SET ROWCOUNT @maxRow

       INSERT INTO @TempItems (

               EmployeeID

              ,EmployeeName

              )

       SELECT *

       FROM Employee

       SET ROWCOUNT @PageSize

       DECLARE @minimumRange BIGINT, @maximumRange BIGINT

       SET @minimumRange = (@PageNo * @PageSize) - @PageSize

       SET @maximumRange = (@PageNo * @PageSize + 1)

       SELECT *

       FROM @TempItems t

       WHERE Rowid BETWEEN @minimumRange AND @maximumRange

       SET ROWCOUNT 0

END