7.6 参数化排序

作者: (美)Itzik Ben-gan... 赵立东译出处:电子工业出版社博文视点　2007-10-27 13:04　砖好评论条　进入论坛

阅读提示：《Microsoft SQL Server 2005技术内幕:T-SQL程序设计》第7章首先简要介绍SQL Server 2005所支持的不同类型的存储过程，然后再深入研究它的细节。本文讲的是参数化排序。

7.6 参数化排序

为了练习到目前为止你学习到的知识，试着完成这个任务：编写一个储存过程usp_GetSortedShippers，它接收Northwind数据库中Shippers表的一个列名称作为其中一个输入（@colname），并从该表返回按输入的列名排序的行。另一个输入（@sortdir）表示排序的方向,‘A’表示按升顺排序,‘D’表示按降序排序。编写该存储过程时要注意它的性能，即，尽可能的使用索引（例如，排序列上的聚集或非聚集覆盖索引）。

代码清单7-4是该任务建议的第一个解决方案

代码清单7-4 参数化排序，解决方案1

USE Northwind;
GO
IF OBJECT_ID('dbo.usp_GetSortedShippers') IS NOT NULL
DROP PROC dbo.usp_GetSortedShippers;
GO
CREATE PROC dbo.usp_GetSortedShippers
@colname AS sysname, @sortdir AS CHAR(1) = 'A'
AS

IF @sortdir = 'A'
SELECT ShipperID, CompanyName, Phone
FROM dbo.Shippers
ORDER BY
CASE @colname
WHEN N'ShipperID'   THEN CAST(ShipperID AS SQL_VARIANT)
WHEN N'CompanyName' THEN CAST(CompanyName AS SQL_VARIANT)
WHEN N'Phone'       THEN CAST(Phone AS SQL_VARIANT)
END
ELSE
SELECT ShipperID, CompanyName, Phone
FROM dbo.Shippers
ORDER BY
CASE @colname
WHEN N'ShipperID'   THEN CAST(ShipperID AS SQL_VARIANT)
WHEN N'CompanyName' THEN CAST(CompanyName AS SQL_VARIANT)
WHEN N'Phone'       THEN CAST(Phone AS SQL_VARIANT)
END DESC;
GO

该解决方案使用IF语句根据请求的排序方向确定要执行的查询。两个查询的唯一区别是它们的排序表达式不同，一个使用升序，一个使用降序。两个查询都使用CASE表

达式根据输入的列名返回合适的列值。

注意 SQL Server根据表达式的结果中优先级最高的数据类型确定CASE表达式结果的数据类型，而不是按实际返回结果的数据类型确定。这就意味着，如果CASE表达式的一个THEN子句返回VARCHAR(30)值，而另一个返回INT值，表达式的结果将总是INT，因为INT的优先级比VARCHAR高。如果实际返回VARCHAR(30)值，SQL Server将尝试转换它。如果该值不可转换，将产生一个运行时错误。如果可以转换，它被转换为INT类型，当然，这可能会导致和原始值不一样的排序行为。

为避免这个问题，我把所有可能的返回值都转换为SQL_VARIANT。SQL Server将把CASE表达式的数据类型设置为SQL_VARIANT，但在SQL_VARIANT内部会保存原始数据类型。

运行下面的代码测试该解决方案，按ShipperID降序排序发货人，生成的输出如表7-11所示。

EXEC dbo.usp_GetSortedShippers N'ShipperID', N'D';

这个存储过程定义了整型输入参数@d，默认值为0。声明一个时间类型的局部变量@odate，它被赋值为今天的日期减去@d天之后的日期。然后执行查询，返回OrderDate

大于或等于@odate的所有订单。调用这个存储过程并使用@d的默认值，将生成表7-10中所示的输出。

EXEC dbo.usp_GetOrders;

表7-11 usp_GetSortedShippers 的输出

ShipperID	CompanyName	Phone
3	Federal Shipping	(503) 555-9931
2	United Package	(503) 555-3199
1	Speedy Express	(503) 555-9831

该输出逻辑上是正确的，但要注意该存储过程生成的计划，如图7-4所示。

图7-4 执行计划显示一个表扫描（无序聚集索引扫描）和一个排序操作运算符

如果你在排序列上执行操作，优化器不依赖于索引维护的顺序。计划执行表扫描（无序的聚集索引扫描）后又执行显式的排序操作。对于该查询，最佳计划应该在ShipperID列的聚集索引上执行有序的扫描操作，不需要再执行显式的排序操作。

代码清单7-5是该任务的第二个解决方案。

代码清单7-5 参数化排序，解决方案2

ALTER PROC dbo.usp_GetSortedShippers
@colname AS sysname, @sortdir AS CHAR(1) = 'A'
AS

SELECT ShipperID, CompanyName, Phone
FROM dbo.Shippers
ORDER BY
CASE WHEN @colname = N'ShipperID'   AND @sortdir = 'A'
THEN ShipperID   END,
CASE WHEN @colname = N'CompanyName' AND @sortdir = 'A'
THEN CompanyName END,
CASE WHEN @colname = N'Phone'       AND @sortdir = 'A'
THEN Phone       END,
CASE WHEN @colname = N'ShipperID'   AND @sortdir = 'D'
THEN ShipperID   END DESC,
CASE WHEN @colname = N'CompanyName' AND @sortdir = 'D'
THEN CompanyName END DESC,
CASE WHEN @colname = N'Phone'       AND @sortdir = 'D'
THEN Phone       END DESC;
GO

这个解决方案使用CASE的方式更高明些。每组列和排序方向的组合被用作一个CASE表达式。对于所有行，只有匹配指定列名称和排序方向的那个CASE表达式结果为TRUE，其他的CASE表达式都将返回NULL。这意味着只有匹配指定列名称和排序方向的那个表达式将影响输出的顺序。

运行下面的代码测试该存储过程：

EXEC dbo.usp_GetSortedShippers N'ShipperID', N'D';

尽管这个存储过程使用了一个非常有意思的逻辑操作，但这并不会改变一个事实，即你对该列执行操作但未按它的现状排序。这意味着你会得到和前面表7-4所示类似的非最佳计划。

代码清单7-6是该任务的第三个解决方案。

代码清单7-6 参数化排序，解决方案3

ALTER PROC dbo.usp_GetSortedShippers
@colname AS sysname, @sortdir AS CHAR(1) = 'A'
AS

IF @colname NOT IN (N'ShipperID', N'CompanyName', N'Phone')
BEGIN
RAISERROR('Possible SQL injection attempt.', 16, 1);
RETURN;
END 
DECLARE @sql AS NVARCHAR(4000);

SET @sql = N'SELECT ShipperID, CompanyName, Phone
FROM dbo.Shippers
ORDER BY '
+ QUOTENAME(@colname)
+ CASE @sortdir WHEN 'D' THEN N' DESC' ELSE '' END
+ ';';

EXEC sp_executesql @sql;
GO

这个解决方案使用动态执行，把输入的列名和排序方向串联到查询的ORDER BY子句。这个解决方案在性能方面达到了我们的目标。即，如果存在索引它将有效地使用索引。要观察这一点，运行下面的代码。

EXEC dbo.usp_GetSortedShippers N'ShipperID', N'D';

观察图7-5显示地执行计划，计划执行一个有序向下的聚集索引扫描，没有使用排序运算符，它是该输入的最佳计划。

图7-5 执行计划显示了一个有序向后的聚集索引扫描

这个解决方案的另外一个好处是它非常容易维护。它的缺点是因为使用了动态执行，这会涉及很多安全问题（例如，如果未验证输入可能会产生所有权链（ownership chaining）和SQL注入问题）。关于动态执行的安全问题更为详细的信息，请参考第4章。
我将要介绍的第四个解决方案列在代码清单7-7中。
代码清单7-7 参数化排序，解决方案4

CREATE PROC dbo.usp_GetSortedShippers_ShipperID_A
AS
SELECT ShipperID, CompanyName, Phone
FROM dbo.Shippers
ORDER BY ShipperID;
GO
CREATE PROC dbo.usp_GetSortedShippers_CompanyName_A
AS
SELECT ShipperID, CompanyName, Phone
FROM dbo.Shippers
ORDER BY CompanyName;
GO
CREATE PROC dbo.usp_GetSortedShippers_Phone_A 
AS
SELECT ShipperID, CompanyName, Phone
FROM dbo.Shippers
ORDER BY Phone;
GO
CREATE PROC dbo.usp_GetSortedShippers_ShipperID_D
AS
SELECT ShipperID, CompanyName, Phone
FROM dbo.Shippers
ORDER BY ShipperID   DESC;
GO
CREATE PROC dbo.usp_GetSortedShippers_CompanyName_D
AS
SELECT ShipperID, CompanyName, Phone
FROM dbo.Shippers
ORDER BY CompanyName DESC;
GO
CREATE PROC dbo.usp_GetSortedShippers_Phone_D
AS
SELECT ShipperID, CompanyName, Phone
FROM dbo.Shippers
ORDER BY Phone       DESC;
GO

ALTER PROC dbo.usp_GetSortedShippers
@colname AS sysname, @sortdir AS CHAR(1) = 'A'
AS

IF @colname = N'ShipperID'        AND @sortdir = 'A'
EXEC dbo.usp_GetSortedShippers_ShipperID_A;
ELSE IF @colname = N'CompanyName' AND @sortdir = 'A'
EXEC dbo.usp_GetSortedShippers_CompanyName_A;
ELSE IF @colname = N'Phone'       AND @sortdir = 'A'
EXEC dbo.usp_GetSortedShippers_Phone_A;
ELSE IF @colname = N'ShipperID'   AND @sortdir = 'D'
EXEC dbo.usp_GetSortedShippers_ShipperID_D;
ELSE IF @colname = N'CompanyName' AND @sortdir = 'D'
EXEC dbo.usp_GetSortedShippers_CompanyName_D;
ELSE IF @colname = N'Phone'       AND @sortdir = 'D'
EXEC dbo.usp_GetSortedShippers_Phone_D;
GO

该解决方案乍一看好像有些幼稚。它为每种可能的输入组合创建一个使用静态查询的存储过程。然后，把usp_GetSortedShippers作为一个导航器。使用一系列IF/ELSE IF语句检查每种可能的输入组合，然后为每个组合显式地调用合适的存储过程，它比前面的解决方案更长，而且需要更多的维护，但它使用静态查询，可以生成最佳计划。每个查询都有自己的计划，对于相同的查询可以重用之前缓存的计划。
要测试该存储过程，运行下面的代码。

EXEC dbo.usp_GetSortedShippers N'ShipperID', N'D';

你将得到该输入的最佳计划，与前面图7-5所示的计划相同。
完成后，运行下面的代码进行清理：

IF OBJECT_ID('dbo.usp_GetSortedShippers') IS NOT NULL
DROP PROC dbo.usp_GetSortedShippers;
IF OBJECT_ID('dbo.usp_GetSortedShippers_ShipperID_A') IS NOT NULL
DROP PROC dbo.usp_GetSortedShippers_ShipperID_A;
IF OBJECT_ID('dbo.usp_GetSortedShippers_CompanyName_A') IS NOT NULL
DROP PROC dbo.usp_GetSortedShippers_CompanyName_A;
IF OBJECT_ID('dbo.usp_GetSortedShippers_Phone_A') IS NOT NULL
DROP PROC dbo.usp_GetSortedShippers_Phone_A;
IF OBJECT_ID('dbo.usp_GetSortedShippers_ShipperID_D') IS NOT NULL
DROP PROC dbo.usp_GetSortedShippers_ShipperID_D;
IF OBJECT_ID('dbo.usp_GetSortedShippers_CompanyName_D') IS NOT NULL
DROP PROC dbo.usp_GetSortedShippers_CompanyName_D;
IF OBJECT_ID('dbo.usp_GetSortedShippers_Phone_D') IS NOT NULL
DROP PROC dbo.usp_GetSortedShippers_Phone_D;

【责任编辑：雪花 TEL：（010）68476606-8007】

回书目 上一节 下一节

关于排序参数 SQL Server 2005 T-SQL 程序设计 Microsoft SQL Server 2005技术内幕:T-SQL程序设计的

文章

博文

帖子

· 7.4.3 参数嗅探问题(10/27)

· 7.9 结论(10/27)

· 7.8 CLR存储过程(2)(10/27)

· 7.8 CLR存储过程(1)(10/27)

· 7.7 动态Pivot(2)(10/27)

· 程序设计如何学？

· Java:基于栈和队列的排序算法

· IIS中WEB服务器的日志存放到SQL Server 2005中

· SQL Server 2005系列教学(18) 自动化管理

· SQL Server 2005数据库快照

· PowerBuilder程序设计语言之名词

· SQL Server 2005中的T-SQL增强

· 请问如何对SYBASE数据库的性能参数进行调整

· 上海Qoolu网络诚聘FlashAS3.0程序设计/Flash动..

· 急聘C程序设计人员

专题

我也说两句

叫好

拍砖

中国最大的网络技术网站 ·
技术成就梦想

·假期读书充电

· 第六章　你能帮我吗？..
· Linux笔试面试题选摘测..
· 08年5月软考网管上午真..
· 性能测试从零开始目录
· 08年5月软考网工上午真..
· 上周拒绝服务攻击（DDo..

· 08年5月各大网上书店及..
· 2008年5月24日软考试题..
· 软件设计师专家临考模..
· 上周网络管理员专家自..
· 网络工程师自测获奖名..
· 08年4月各大网上书店及..

排行榜

·假期读书充电 (查看6738次)
·1.2 网络定义 (查看1197次)
·《网管员必读—网络应用》自测.. (查看1090次)
·SQL Server入门到精通技术自测.. (查看1088次)
·历次技术自测获奖网友详细信息 (查看1040次)

·SQL Server入门到精通技术自测试题答案 (8个砖)
·超级网管员——网络应用技术自测试题答案 (6个砖)
·历次技术自测获奖网友详细信息 (4个砖)
·网管员全真面试题自测获奖结果及答案解析 (4个砖)
·免费讲座：搜索引擎营销打造成功网站 (3个砖)

·假期读书充电 (13个好)
·SQL Server入门到精通技术自测试题答案 (4个好)
·Visual C++数字图像处理开发入门与编程实践 (3个好)
·路由器配置自测获奖结果及答案解析 (3个好)
·《游戏开发核心技术-剧本与角色创造》阅读不枯燥 (3个好)

·Ajax+JSP网站开发从入门到精通 (06月)
·ASP.NET Ajax网站开发从入门到精通 (06月)
·非常网管——网络管理从入门到精通 (06月)
·ANT IN ACTION 第2版中文版 (06月)
·欺骗的艺术 (06月)

·ARP协议及欺骗原理
·独家体验：在西三环亲历北京免费无..

· 周末病毒预报：视频宝..
· NAC精要指南:什么是网..
· 布线测试中的常见问题..
· ASP.NET Ajax网站开发..
· 网管员必读：网络链路..
· SQL Server 2008中不推..
· 路由器故障排除实例讲解
· Cisco 认证考试费用全..

· 微软针对XP SP2的远程..
· 山东临沂网通联合公安..
· Cisco考试费用全线涨价..
· 企业无线网络设置10大..
· Windows Server 2003网..
· 上海宣判三起网络知识..
· 微软Sever 2008自带虚..
· 金山信息安全技术公司..

订阅技术快讯

电子杂志下载

名称：SQL Server数据库管理精品黄皮书
简介：书中文章经过精挑细选，便于用户能根据自己的实际工作和学习，快速在本书寻找到相关资料。内容涵盖了SQL Server的安装与升级、语句查询、数据备份和恢复、自动化任务、数据同步、数据字典、安全和预防、性能和优化、集群等各方面应用信息，以及DBA管理人员在数据库管理工作中

名称：2007路由技术大全
简介：《2007路由技术大全》由51CTO.com网站特别策划制作，该书包括路由器技术、路由器产品、路由器配置、安全设置、路由器故障处理、路由器密码恢复，以及广大网友在实践使用中的心得经验和技巧文章，内容注重实用性，适用于初学者入门，也适合多年从业者提高，是一本实践和理论完

名称：网络安全精品应用黄皮书
简介：《2007精品网络安全黄皮书》包括了9个大类24个小类， 800余篇文章，内容包含了熊猫烧香病毒、DDOS攻击、ARP病等热点问题的介绍及解决方案。从病毒查杀、防范、系统、数据等各方面的安全设置到黑客技术的了解、防范，涉及到了安全应用的全部领域, 由浅至深内容全面。

专题

最多好

最多砖