毫秒级百万数据分页存储过程
然后创建分页存储过程;这个是基于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
ONGOSET QUOTED_IDENTIFIER
ONGO--
=============================================--
Author: im531 --
Create date: 2010-10-25--
Description: SQL 2005 ROW_NUMBER 分页--
Modify Date: 2010-10-26--
=============================================CREATE PROCEDURE [dbo].[im531_Page] @TabeNameAS NVARCHAR(50), @FieldsAS NVARCHAR(1024), @SearchWhereAS NVARCHAR(1024), @OrderFieldsAS NVARCHAR(1024), @pageNumberAS INT, @pageAS INTASBEGIN SET NOCOUNTON; DECLARE @sqlTypeAS TINYINT SET @sqlType
= 0 --
0 BETWEEN 1 TOP DECLARE @sqlAS 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 |
