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],在行數選擇靠前時訪問效率高。

Edit in Browser	/_layouts/images/icxddoc.gif	/Departments/dev2/_layouts/formserver.aspx?XsnLocation={ItemUrl}&OpenIn=Browser	0x0	0x1	FileType	xsn	255
Edit in Browser	/_layouts/images/icxddoc.gif	/Departments/dev2/_layouts/formserver.aspx?XmlLocation={ItemUrl}&OpenIn=Browser	0x0	0x1	ProgId	InfoPath.Document	255
Edit in Browser	/_layouts/images/icxddoc.gif	/Departments/dev2/_layouts/formserver.aspx?XmlLocation={ItemUrl}&OpenIn=Browser	0x0	0x1	ProgId	InfoPath.Document.2	255
Edit in Browser	/_layouts/images/icxddoc.gif	/Departments/dev2/_layouts/formserver.aspx?XmlLocation={ItemUrl}&OpenIn=Browser	0x0	0x1	ProgId	InfoPath.Document.3	255
Edit in Browser	/_layouts/images/icxddoc.gif	/Departments/dev2/_layouts/formserver.aspx?XmlLocation={ItemUrl}&OpenIn=Browser	0x0	0x1	ProgId	InfoPath.Document.4	255
View in Web Browser	/_layouts/images/ichtmxls.gif	/Departments/dev2/_layouts/xlviewer.aspx?listguid={ListId}&itemid={ItemId}&DefaultItemOpen=1	0x0	0x1	FileType	xlsx	255
View in Web Browser	/_layouts/images/ichtmxls.gif	/Departments/dev2/_layouts/xlviewer.aspx?listguid={ListId}&itemid={ItemId}&DefaultItemOpen=1	0x0	0x1	FileType	xlsb	255
Snapshot in Excel	/_layouts/images/ewr134.gif	/Departments/dev2/_layouts/xlviewer.aspx?listguid={ListId}&itemid={ItemId}&Snapshot=1	0x0	0x1	FileType	xlsx	256
Snapshot in Excel	/_layouts/images/ewr134.gif	/Departments/dev2/_layouts/xlviewer.aspx?listguid={ListId}&itemid={ItemId}&Snapshot=1	0x0	0x1	FileType	xlsb	256