Server Side Paging in SQL Server 2005
Ex:: In Case of Single Table
- Row Count:: Should call one time only at the time of Page Load
CREATE PROCEDURE [dbo].[DmnRowCountTest]
— @start int=0,
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT Count(<Field>) AS count
FROM [dbo].[<TableName>] (NOLOCK)
END
- Paging::
CREATE Procedure [dbo].[DmnPagingTest] –110001,2,15,Null
(
@PageNo Int,
@PageSize Int
)
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @RowStart Int
DECLARE @RowEnd Int
SET @RowStart=(@PageSize*@PageNo)-(@PageSize-1)
SET @RowEnd=@PageSize*@PageNo;
SELECT * –Column List
From
(
SELECT
Row_Number() over (order by Location_ID asc)RowNo –RowNo
,[<tablename>].[<Field1>]
, [<tablename>].[<Field2>]
FROM
FROM [dbo].[<TableName>] (NOLOCK)
)TBL
WHERE
[TBL].[RowNo]>=@RowStart AND [TBL].[RowNo]<=@RowEnd
END
Ex: in Case of Join
In case of Join two table, first all the record with rowno should store in a temp table then should apply where condition, direct query take long time.
CREATE PROCEDURE [dbo].[DmnLookUp]
(
@PageNo Numeric(16)
,@PageSize Numeric(16)
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @StartRow Numeric(16)
DECLARE @EndRow Numeric(16)
SET @StartRow=(@PageNo*@PageSize)-(@PageSize-1)
SET @EndRow = @PageNo*@PageSize
BEGIN
IF EXISTS(SELECT * FROM [dbo].[sys.objects] WHERE [sys.objects].[Object_Id] = OBJECT_ID(N’#JATC’)) DROP TABLE #JATC
SELECT
Row_Number() OVER (ORDER BY l.location_id asc) [RowNo]
,[<tablename>].[<Field1>]
,[<tablename>].[<Field2>]
INTO #JATC
FROM
[dbo].[<Table1>] D (NOLOCK)
INNER JOIN [dbo].[<Table1>] L (Nolock)
ON [L].[<MasterColumn>]=[D].[ <CheldColumn>]
SELECT
[ROWNO]
,[<tablename>].[<Field1>]
,[<tablename>].[<Field2>]
FROM #JATC
WHERE
[RowNo]>=@StartRow
AND
[RowNo]<=@EndRow
END
Enjoy!