SQL参数化排序详解

编写一个储存过程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';

表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;