When you are fetching large "datasets" using ROW_NUMBER(), you might experience a long
Here is a small demonstration :
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.
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
DECLARE @minimumRange BIGINT, @maximumRange BIGINT
SET @minimumRange = (@PageNo * @PageSize) - @PageSize
SET @maximumRange = (@PageNo * @PageSize + 1)
SELECT *
FROM @TempItems t
SET ROWCOUNT 0
END
