今天看了看2005中T-SQL的新增功能,心血来潮,试了试利用CTE方法做的分页,感觉还不错。下面贴一下TopN方法和CTE方法的数据分页的存储过程,写的还算是比较通用。性能分析我以后会进一步研究一下。
代码还是先来一个测试用表,代码如下:
1SET ANSI_NULLS ON
2GO
3SET QUOTED_IDENTIFIER ON
4GO
5CREATE TABLE [dbo].[testTable](
6 [id] [int] IDENTITY(1,1) NOT NULL,
7 [testDate] [datetime] NOT NULL CONSTRAINT [DF_testTable_testDate] DEFAULT (getdate()),
8 [name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
9 [description] [nchar](50) COLLA
今天看了看2005中T-SQL的新增功能,心血来潮,试了试利用CTE方法做的分页,感觉还不错。下面贴一下TopN方法和CTE方法的数据分页的存储过程,写的还算是比较通用。性能分析我以后会进一步研究一下。
代码还是先来一个测试用表,代码如下:
1
SET ANSI_NULLS ON
2
GO
3
SET QUOTED_IDENTIFIER ON
4
GO
5
CREATE TABLE [dbo].[testTable](
6
[id] [int] IDENTITY(1,1) NOT NULL,
7
[testDate] [datetime] NOT NULL CONSTRAINT [DF_testTable_testDate] DEFAULT (getdate()),
8
[name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
9
[description] [nchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
10
[orderColum] [float] NOT NULL,
11
CONSTRAINT [PK_testTable] PRIMARY KEY CLUSTERED
12
(
13
[id] ASC
14
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
15
) ON [PRIMARY]
16![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
填充一下表,加了3W条记录,倒是不多。
1
declare @i int
2
set @i = 1
3
while @i < 30001
4
begin
5
INSERT INTO testTable([name],[description],[orderColum])
6
VALUES('names', 'descriiption', @i * rand())
7
set @i = @i +1
8
end
1、TopN方法:这个方法就不多说了,相当普遍,页码少的时候比较好使。
[代码]
1
=============================================
2
-- Author: <Author,,microant>
3
-- Create date: <2007年7月5日,,>
4
-- Description: <Description,selectTopN分页方法,>
5
-- =============================================
6
CREATE PROCEDURE [dbo].[sp_SelectTopN](
7
-- Add the parameters for the stored procedure here
8
@TableName varchar(200) = 'testTable', --表名
9
@PageSize int = 15, --页面大小
10
@PageIndex int =2 , --页面的序号
11
--@IsCountNull bit =1, --返回记录是否为空
12
@IsAsc bit = 1 , --是否卫升序,升序为1,降序为0
13
@OderColumName varchar(200) = null, --排序字段名
14
@KeyID varchar(50) = 'id', --主键
15
@Conditions varchar(500) = null --查询条件
16
)
17
AS
18
set nocount on
19
20
declare @strSql nvarchar(1000)
21
declare @tempstr nvarchar(1000)
22
declare @orderstr nvarchar(400)
23
24
--判断排序方式,@IsAsc =1 升序, 0降序,设置排序语句
25
if @IsAsc = 1
26
begin
27
if(@OderColumName is null or @OderColumName = '')
28
set @orderstr = ' order by ' + @KeyID + ' asc'
29
else
30
set @orderstr = ' order by ' + @OderColumName + ' asc'
31
end
32
else
33
begin
34
if(@OderColumName is null or @OderColumName = '')
35
set @orderstr = ' order by ' + @KeyID + ' desc'
36
else
37
set @orderstr = ' order by ' + @OderColumName + ' desc'
38
end
39![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
--查询条件是否添加
41
if @Conditions is null
42
begin
43
set @tempstr = 'select top ' + str(@PageSize*@PageIndex) +' ' +@KeyID +' from ' + @TableName + @orderstr;
44
set @strSql = 'select top ' + str(@PageSize)+ ' * from ' + @TableName + ' where ' + @KeyID +' not in ( ' + @tempstr +')' + @orderstr;
45
end
46
else
47
begin
48
set @tempstr = 'select top ' + str(@PageSize*@PageIndex) +' ' +@KeyID +' from ' + @TableName+ ' where ' + @Conditions + ' ' + @orderstr;
49
set @strSql = 'select top ' + str(@PageSize)+ ' * from ' + @TableName + ' where ' + @Conditions + ' and ' + @KeyID +' not in (' + @tempstr +')' + @orderstr;
50
end
51
print @strSql
52
exec sp_executesql @strSql
53
set nocount off
54![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
[测试]
1
DECLARE @return_value int
2
EXEC @return_value = [dbo].[sp_SelectTopN]
3
@TableName = N'testTable',
4
@PageSize = 30,
5
@PageIndex = 4,
6
@IsAsc = 0,
7
@OderColumName = N'orderColum',
8
@KeyID = N'id',
9
@Conditions = 'id > 50'
10
SELECT 'Return Value' = @return_value
11
go
12![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2、CTE方法:
CTE(常见表表达式)是一个可以由定义语句引用的临时命名的结果集,和临时表比较相似。一般形式如下,
WITH <cte_alias>(<column_aliases>)
AS
(
<cte_query>
)
SELECT *
FROM <cte_alias>
但是比临时表慢,但是考虑临时表会增大日志文件的大小,引起大量IO,CTE也就有他自己的优势。性能分析详情参见http://blog.csdn.net/yizhu2000/archive/2007/06/03/1636573.aspx
[代码]
1
-- =============================================
2
-- Author: <Author,,microant>
3
-- Create date: <Create Date,,20070705>
4
-- Description: <Description,,CTE分页>
5
-- =============================================
6
CREATE PROCEDURE [dbo].[sp_CTE](
7
-- Add the parameters for the stored procedure here
8
@TableName varchar(200) = 'testTable', --表名
9
@PageSize int = 15, --页面大小
10
@PageIndex int =2 , --页面的序号
11
--@IsCountNull bit =1, --返回记录是否为空
12
@IsAsc bit = 1 , --是否卫升序,升序为1,降序为0
13
@OderColumName varchar(200) = null, --排序字段名
14
@KeyID varchar(50) = 'id', --主键
15
@Conditions varchar(500) = null --查询条件
16
)
17
AS
18![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
-- SET NOCOUNT ON added to prevent extra result sets from
20
-- interfering with SELECT statements.
21
SET NOCOUNT ON;
22![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
declare @strSql nvarchar(1000)
24
declare @tempstr nvarchar(1000)
25
declare @orderstr nvarchar(400)
26
declare @ctestr nvarchar(400)
27
28
--判断排序方式,@IsAsc =1 升序, 0降序,设置排序语句
29
if @IsAsc = 1
30
begin
31
if(@OderColumName is null or @OderColumName = '')
32
set @orderstr = ' order by ' + @KeyID + ' asc'
33
else
34
set @orderstr = ' order by ' + @OderColumName + ' asc'
35
end
36
else
37
begin
38
if(@OderColumName is null or @OderColumName = '')
39
set @orderstr = ' order by ' + @KeyID + ' desc'
40
else
41
set @orderstr = ' order by ' + @OderColumName + ' desc'
42
end
43
44
--CTE
45
set @ctestr ='with Table_CET
46
as
47
(
48
select
49
CEILING((ROW_NUMBER() OVER (' + @orderstr + '))/' + str(@PageSize) + ') as page_num, *
50
from ' + @TableName +
51
')' ;
52
53
54
set @strSql = @ctestr + 'select * from Table_CET where page_num = ' + str(@PageIndex)+ ' and ' + @Conditions;
55
56
print @strSql
57
begin
58
exec sp_executesql @strSql;
59
end
[测试]
1
DECLARE @return_value int
2
EXEC @return_value = [dbo].[sp_CTE]
3
@TableName = N'testTable',
4
@PageSize = 30,
5
@PageIndex = 4,
6
@IsAsc = 0,
7
@OderColumName = N'orderColum',
8
@KeyID = N'id',
9
@Conditions = 'id > 50'
10
SELECT 'Return Value' = @return_value
11
GO
3、以前没太关注SQL server 2005的新功能,一直在用MySQL,或者2000,每天关注Oracle但是很不幸都没机会做过什么。不曾想加了不少共能,得好好看看。师兄再给我说他们在用Informax等一系列工具做数据仓库,很高兴听到几个不懂得名词,很不幸没有机会见识见识。还是务实点看看2005提供了啥吧。
T-SQL也出现了不少新功,CTE就不错看着,比较好使,具体参见https://www.microsoft.com/china/msdn/library/data/sqlserver/05TSQLEnhance.mspx?mfr=true