Paging Records Using SQL Server 2005 Database - ROW_NUMBER Function
by ( )
[@more@]SQL Server 2005 has a ROW_NUMBER Function that can help with paging records for you database applications. ROW_NUMBER returns a sequential number, starting at 1, for each row returned in a resultset.
If I want the first page of 10 records from my log file sorted by Date DESC, I can use the ROW_NUMBER FUNCTION as follows:
SELECT Description, Date FROM (SELECT ROW_NUMBER() OVER (ORDER BY Date DESC) AS Row, Description, Date FROM LOG) AS LogWithRowNumbers WHERE Row >= 1 AND Row <= 10
The second page of 10 records would then be as follows:
SELECT Description, Date FROM (SELECT ROW_NUMBER() OVER (ORDER BY Date DESC) AS Row, Description, Date FROM LOG) AS LogWithRowNumbers WHERE Row >= 11 AND Row <= 20
If you have a lot of records, using TOP X in the inner SELECT clause may speed up things a bit as there is no use returning 1000 records if you are only going to grab records 11 through 20:
SELECT Description, Date FROM (SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY Date DESC) AS Row, Description, Date FROM LOG) AS LogWithRowNumbers WHERE Row >= 11 AND Row <= 20
We can rap this up in a Stored Procedure as follows:
CREATE PROCEDURE dbo.ShowLog @PageIndex INT, @PageSize INT AS BEGIN WITH LogEntries AS ( SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description FROM LOG) SELECT Date, Description FROM LogEntries WHERE Row between(@PageIndex - 1) * @PageSize + 1
and @PageIndex*@PageSize END
It is only available in SQL Server 2005, but it is a heck of a lot easier and more intuitive than creating temp tables and using other stored procedures that I have used in the past. However, if you want to target your application for SQL Server 2000 use, I would stick with a record paging solution that works for both SQL Server 2005 and SQL Server 2000 Databases.
Source: ( )
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/10771986/viewspace-968265/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Custom Paging in ASP.NET 2.0 with SQL Server 2005ASP.NETSQLServer
- Sql Server 2005 row_number()分頁效能測試SQLServer
- Guide to Database Migration from Microsoft SQL Server using MySQL WorkbenchGUIIDEDatabaseROSServerMySql
- SQL server 2005 expressSQLServerExpress
- Sql Server 2005函式SQLServer函式
- SQL SERVER 2005 配置-saSQLServer
- SQL SERVER2005建Link ServerSQLServer
- Moving the tempdb database(SQL server)DatabaseSQLServer
- Sql Server 2005新增T-sql特性SQLServer
- Using Excel to generate Inserts for SQL ServerExcelSQLServer
- SQL Server中row_number函式的常見用法SQLServer函式
- XML Support in Microsoft SQL Server 2005XMLROSSQLServer
- PowerShell連線 SQL Server 2005SQLServer
- Monitoring Tempdb in SQL Server 2005SQLServer
- 微軟SQL Server 2005速成版微軟SQLServer
- SQL Server2005快捷鍵SQLServer
- SQL SERVER 2005映象實驗SQLServer
- SQL Server 2005 Service Broker 初探SQLServer
- SQL Server 2005 Express Edition 概述SQLServerExpress
- SQL Server 2005:清空plan cacheSQLServer
- SQL Server 2005中修改 Server Collation的方法SQLServer
- SQL SERVER 排序函式ROW_NUMBER、RANK、DENSE_RANK、NTILESQLServer排序函式
- sql server 2005資料庫快照SQLServer資料庫
- 配置SQL Server 2005伺服器SQLServer伺服器
- SQL SERVER 2005表分割槽功能SQLServer
- SQL Server 2005動態管理物件SQLServer物件
- 實戰 SQL Server 2005 映象配置SQLServer
- Sql server 2005中output用法解析SQLServer
- Sql Server 2005 日誌壓縮SQLServer
- SQL SERVER 2005 日誌收縮SQLServer
- sql server 2005使用點滴(1)SQLServer
- SQL Server 2005 Cluster 叢集部署SQLServer
- SQL Server 2005鎖的問題SQLServer
- 【清清月兒】用SQL 2005的ROW_NUMBER() 實現分頁功能SQL
- 已安裝 SQL Server 2005 Express 工具。若要繼續,請刪除 SQL Server 2005 Express 工具SQLServerExpress
- How to prevent blocking in your SQL Server databaseBloCSQLServerDatabase
- SQL Server 2005效能調整二(zt)SQLServer
- SQL Server 2005效能調整一(zt)SQLServer