毫秒级百万数据分页存储过程

然后创建分页存储过程;这个是基于SQL 2005 的ROW_NUMBER的, SQL 2000 不适用; SQL 2008 我没有安装,所以也没有测试过!估计应该可以运行.

 2种算法可以选择 1. 使用  BETWEEN   ;  2.  TOP  ; 代码中已经注释了.

我测试了一下2种方法基本没有什么区别;代码如下:

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62

USE [DATA_TEMP] GO /****** 对象:  StoredProcedure [dbo].[im531_Page]    脚本日期: 11/26/2010 10:52:35 ******/ SET  ANSI_NULLS ON GO SET  QUOTED_IDENTIFIER ON GO   -- ============================================= -- Author:      im531 -- Create date: 2010-10-25 -- Description: SQL 2005 ROW_NUMBER 分页 -- Modify Date: 2010-10-26 -- =============================================   CREATE  PROCEDURE  [dbo].[im531_Page]      @TabeName AS  NVARCHAR(50),      @Fields AS  NVARCHAR(1024),      @SearchWhere AS  NVARCHAR(1024),      @OrderFields AS  NVARCHAR(1024),      @pageNumber AS  INT ,      @page AS  INT AS BEGIN      SET  NOCOUNT ON ;      DECLARE  @sqlType AS  TINYINT      SET  @sqlType = 0  -- 0 BETWEEN   1 TOP      DECLARE  @sql AS  NVARCHAR( MAX )      IF @SearchWhere <> ''  SET  @SearchWhere = ' WHERE '  + @SearchWhere      IF @page < 2 SET  @page = 1      IF @page = 1          BEGIN              SET  @sql = 'SELECT TOP '  + CONVERT (NVARCHAR(20),@pageNumber) + ' '  + @Fields                  + 'FROM ['  + @TabeName + '][a] WITH(NOLOCK) '  + @SearchWhere + ' ORDER BY '  + @OrderFields          END      ELSE          BEGIN              --临时表              SET  @sql = ';WITH [Page_____Table] AS('                  + 'SELECT ROW_NUMBER() OVER(ORDER BY '  + @OrderFields + ') AS [RowNow],'  + @Fields                  + 'FROM [' + @TabeName + ' ][a] WITH(NOLOCK) '  + @SearchWhere                  + ')'              --查找当前页面记录              IF @sqlType = 0                  BEGIN                  -- A USE BETWEEN                      SET  @sql = @sql + 'SELECT *'                          + 'FROM [Page_____Table] WITH(NOLOCK)'                          + 'WHERE [RowNow] BETWEEN '  + CONVERT (NVARCHAR(20),(@page - 1) * @pageNumber + 1) + ' AND '  + CONVERT (NVARCHAR(20),@page * @pageNumber)                          + 'ORDER BY [RowNow]'                  END              ELSE                  BEGIN                  -- B USE TOP                      SET  @sql = @sql + 'SELECT TOP '  + CONVERT (NVARCHAR(20),@pageNumber) + ' *'                          + 'FROM [Page_____Table] WITH(NOLOCK)'                          + 'WHERE [RowNow] > '  + CONVERT (NVARCHAR(20),(@page - 1) * @pageNumber)                          + 'ORDER BY [RowNow]'                  END          END      EXEC (@sql) END