Process one:
Select Top N records * from article table where ID not in (select top m record ID from article table ORDER BY id DESC) ORDER BY id DESC
Process two:
Select Top N records * from article table where ID < (select min (id) from (select top m record ID from article table ORDER by id desc) as tbltmp) or Der by id Desc
--Simple and universal
CREATE PROCEDURE [dbo]. [PageView]
@select VARCHAR (max),
@CurrentPage INT,
@PageSize INT
As
BEGIN
DECLARE @sql NVARCHAR (max)
DECLARE @RecordCurrent INT
DECLARE @PageCount INT
DECLARE @RecordCount INT
SET NOCOUNT on
Set @sql = ' Select @RecordCount =count (*) from (' [email protected]+ ') a '
EXEC sp_executesql @sql, N ' @RecordCount int output ', @RecordCount output
SET @PageCount = (@[email protected])/@PageSize
IF ISNULL (@CurrentPage, 0) <1
SET @CurrentPage =1
ELSE if ISNULL (@CurrentPage, 0) > @PageCount
SET @[email protected]
SELECT @CurrentPage as CurrentPage, @RecordCount as RecordCount, @PageSize as PageSize, @PageCount as PageCount
Set @sql = ' select * FROM (' [email protected]+ ') A where rownumber between ' +cast ((@CurrentPage-1) * @PageSize +1 as varchar) + ' and ' +cast (@CurrentPage * @PageSize as varchar)
EXEC (@sql)
END
--A stored procedure that uses the not in method
Create PROCEDURE Getpagedatabynotin
@PageIndex int,/**//* Current Page */
@PageSize int/**//*/Page Size */
As
DECLARE @starttime datetime
Set @starttime =getdate ()
IF @PageIndex > 0
BEGIN
SET NOCOUNT on;
DECLARE @PageLowerBound int
DECLARE @StartID int
DECLARE @sql varchar (225)
SET @PageLowerBound = @PageSize * (@PageIndex-1)
IF @PageLowerBound <1
SET @PageLowerBound =1
Print @PageLowerBound
Select Top (@PageSize) * from table where [ar_id] not in (select Top ((@PageSize) * (@PageIndex-1)) ar_id from table)
EXEC (@sql)
SET NOCOUNT off;
END
print ' time consuming = ' +convert (varchar (), DATEDIFF (MS, @starttime, GETDATE ()))
-------------------------------------------------------------------------------------------------------
--Paging stored procedure using ROWCOUNT
Create PROCEDURE Getpagedata
@PageIndex int,/**//* Current Page */
@PageSize int/**//*/Page Size */
As
DECLARE @starttime datetime
Set @starttime =getdate ()
IF @PageIndex > 0
BEGIN
SET NOCOUNT on;
DECLARE @PageLowerBound int
DECLARE @StartID int
DECLARE @sql varchar (225)
SET @BeginIndex = @PageSize * (@PageIndex-1)
IF @BeginIndex <1
SET @BeginIndex =1
SET ROWCOUNT @BeginIndex
SELECT @StartID = [ar_id] FROM table ORDER by ar_id
Print @StartID
SET ROWCOUNT 0
SET @sql = ' select Top ' +str (@PageSize) + ' * from table where [ar_id]>= ' + str (@StartID) + ' ORDER by [ar_id] '
EXEC (@sql)
SET NOCOUNT off;
END
print ' time consuming = ' +convert (varchar (), DATEDIFF (MS, @starttime, GETDATE ()))
-----------------------------------------------------------------------------------------------------
Test query a table with 100W data, showing 10 data per page
Stored Procedure 1th page 10th page 100th page 1000th page No. 5000 page
Getpagedatabynotin 0 0 126 13530 wait more than 2 minutes impatient ....
Getpagedata 0 0 0 16 76
------------------------------------------------------------------------------------------------------------
Last stored procedure used (recommended):
Create PROCEDURE Getpagedata
(
@TableName varchar (,--) Table name
@IDName varchar,--Table primary Key Name
@PageIndex int,--Current page
@PageSize int--per page size
)
As
IF @PageIndex > 0
BEGIN
SET NOCOUNT ON
DECLARE @PageLowerBound int, @StartID int, @sql nvarchar (225)
SET @PageLowerBound = @PageSize * (@PageIndex-1)
IF @PageLowerBound <1
SET @PageLowerBound =1
SET ROWCOUNT @PageLowerBound
SET @sql =n ' SELECT @StartID = [' [email protected]+ '] from ' [E-mail protected]+ ' ORDER by ' [email protected]
EXEC sp_executesql @sql, N ' @StartID int output ', @StartID output
SET ROWCOUNT 0
SET @sql = ' select Top ' +str (@PageSize) + ' * from ' [email protected]+ ' WHERE [' [email protected]+ ']>= ' + str (@StartID) + ' ORDER by [' [email protected]+ '] '
EXEC (@sql)
SET NOCOUNT OFF
END
Several paging stored procedures