ぷ行走de猫げ

在快乐中成长，在快乐中学习！

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],在行數選擇靠前時訪問效率高。
posted @ 2009-01-05 09:29  李天翔  阅读(207)  评论(0)  编辑  收藏  举报