SQL Server2005 分頁

第一種方法
SELECT *
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [reqno]) AS [rownum], *
FROM [dbo].[csreqshortno]
) a
where a.rownum between 110010 and 110030
 
大概需要0～5秒
第二種方法
DECLARE @PageLowerBound int
                 DECLARE @PageUpperBound int
               
                 -- Set the page bounds
                SET @PageLowerBound = 10
                SET @PageUpperBound = 30
 
                -- Create a temp table to store the select results
                Create Table #PageIndex
                (
                    [IndexId] int IDENTITY (1, 1) NOT NULL,
                    [Id] varchar(18)
                )
              
                -- Insert into the temp table
                declare @SQL as nvarchar(4000)
                SET @SQL = 'INSERT INTO #PageIndex (Id)'
                SET @SQL = @SQL + ' SELECT'
                SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
                SET @SQL = @SQL + ' [reqno]'
                SET @SQL = @SQL + ' FROM dbo.csreqshortno'
                SET @SQL = @SQL + ' ORDER BY reqno'
               
                -- Populate the temp table
                exec sp_executesql @SQL
                -- Return paged results
                SELECT O.*
                FROM
                    dbo.csreqshortno O,
                    #PageIndex PageIndex
                WHERE
                    PageIndex.IndexID > @PageLowerBound
                    AND O.reqno = PageIndex.[Id]
               ORDER BY
                   PageIndex.IndexID
               
drop table #PageIndex  
 
大概需要0～3秒
 
第二種方法相對比第一種方法效率高一點,不需要在全表插入列[rownum],在行數選擇靠前時訪問效率高。
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/tianxiang2046/p/1368776.html