关于 T-SQL 的几点小九九 (1)

      忽然想起前不久一个朋友对我所说的话：一个转身或许就意味着一辈子……。时间流逝的总是无情无意，让你没有机会去申诉，转眼距离我上次落笔，已经近3月了，发生了些什么，忙碌着什么，仿佛都不在重要了。

      不在那么煽情了，说说今天想要描述的主题吧，一些关于 SQL SERVER 和 T-SQL 的话题吧。

      先说些基础的话题吧，或许因为过于基础，我们从来没有想过要去深究，从而也就有着许多容易被我们忽视的一些问题：

      1.  T-SQL 和 ANSI SQL 的一次强烈对比

      如果您工作中的数据库环境是SQL SERVER2000，而又困扰于 SELECT TOP 无法满足您的查询记录条数的变量化的需求，您一定会在多方寻找帮助之后，寻觅到 SET ROWCOUNT 来实现您的要求，e.g:

USE OrderForm
IF EXISTS (SELECT [name] FROM sysobjects
    WHERE [name] = 'proc_GetNewsSourceOfSummary' AND type = 'P')
  DROP PROCEDURE proc_GetNewsSourceOfSummary
GO
CREATE PROCEDURE [dbo].[proc_GetNewsSourceOfSummary]
(
  @Count    int,   -- 用于指定返回查询所得记录的条数
  @DataLen  int,   -- 用于指定 DATALENGTH 函数返回的字节数(注意，我转换成varchar数据类型，2个英文字母占用一个字节)
  @Length   int,   -- 用于指定 SUBSTRING 函数返回的字符数或字节数
  @PtCode   varchar(30), -- 用于指定产品编码的字符串
  @newClsCode  varchar(30)  -- 用于指定信息分类编号(从 InfoClass 表中获取)
)
AS
BEGIN
  SET ROWCOUNT @Count

  SELECT a.IT_ID, Summary =
      CASE
          WHEN DATALENGTH(CAST(a.Other AS varchar(120))) <= @DataLen THEN a.Other
          WHEN DATALENGTH(CAST(a.Other AS varchar(120))) > @DataLen THEN SUBSTRING(a.Other, 1, @Length + (@DataLen - 

DATALENGTH(SUBSTRING(CAST(a.Other AS varchar(120)), 1, @Length)))/2) + '…'
      END
  FROM reglogin.dbo.InfoTable AS a
    WHERE a.newClsCode LIKE @newClsCode + '%'
      AND a.PtCode LIKE @PtCode + '%'
      AND a.isMake > 0
      AND a.isDel = 0
      AND a.isCheck = 1
      AND a.IfPage = 1  -- 表示是否将该新闻推荐到相应的频道页
  ORDER BY a.IT_ID DESC

  SET ROWCOUNT 0  -- 执行 SET ROWCOUNT 0 将会关闭 SET ROWCOUNT 选项的影响
END
GO
-- 测试存储过程
EXEC OrderForm.dbo.proc_GetNewsSourceOfSummary  1, 90, 45, '002', '101002016'

      上面的示例是我实际工作中编写的存储过程，无论您是使用 SELECT TOP 也好，还是使用 SET ROWCOUNT也罢，这些都不是问题，关键问题是您是否了解过他们都不属于 ANSI SQL 标准，而是 T-SQL 特有的。

      如何实现上面存储过程中关键部分的 ANSI SQL 的实现方法，为了能简单直白的说明问题，并且能顺利的在真实的数据库环境中运行该 SQL 语句，我只是抽象出上面的存储过程中部分 SQL 语句来说明问题。

-- ANSI SQL 方法
USE OrderForm
IF EXISTS (SELECT [name] FROM sysobjects
      WHERE [name] = 'proc_GetNewsSourceOfID' AND type = 'P')
DROP PROCEDURE proc_GetNewsSourceOfID
GO
CREATE PROCEDURE [dbo].[proc_GetNewsSourceOfID]
(
    @Count    int,   -- 用于指定返回记录的行数
    @newClsCode  varchar(50)  -- 用于指定新闻分类编号
)
AS
BEGIN
SELECT a.IT_ID
    FROM reglogin.dbo.InfoTable AS a
      WHERE ( SELECT COUNT(b.IT_ID)
                FROM reglogin.dbo.InfoTable AS b
                  WHERE b.IT_ID  > a.IT_ID
                    AND b.newClsCode = '102005'
                    AND b.isMake > 0
                    AND b.isDel = 0
                    AND b.isCheck = 1 ) < @Count
      AND a.newClsCode LIKE @newClsCode + '%'
      AND a.isMake > 0
      AND a.isDel = 0
      AND a.isCheck = 1
ORDER BY a.IT_ID DESC
END

-- 测试存储过程
EXEC OrderForm.dbo.proc_GetNewsSourceOfID 5, '102005'

      您或许没有兴趣去测试这条存储过程的性能如何，我想还是有必要将其运行之后生成的“执行计划”展示给大家看看:

      那么如果将符合 ANSI SQL 的存储过程修改为使用 T-SQL 特有的 SET ROWCOUNT 来实现的话，其“执行计划”又会如何呢？或许会让你有点大跌眼镜的感觉，不是吗？
                        

      这样一次强烈的对比，或许并不能说明什么，ANSI SQL 是 T-SQL 的基石，更准确的说法应该是 T-SQL 只是 ANSI SQL 标准的一种具体实现。而 TOP 和 SET ROWCOUNT 无非都是 T-SQL 中一种特有的实现，一切都是为了提高性能而为，我们用之，未尝不可。只是请您记住，在其他的数据库产品或者数据库编程语言中，可不一定有 TOP 和 SET ROWCOUNT 实现哟。

     2. 一个荒唐的 T-SQL 语句

      或许您和我一样，曾经有过编写类似于这样的 T-SQL 语句：

    SELECT *
      FROM (SELECT TOP 100 IT_ID, Title
            FROM reglogin.dbo.InfoTable
            ORDER BY IT_ID DESC) AS newTable;

      上面的 SQL 语句何来的荒唐，那么我借用 T-SQL 大师 Ben-Gan 的描述来说明“荒唐”的由来。

      您是否了解，一句使用了 ORDER BY 子句的标准查询，如：SELECT IT_ID, Title FROM reglogin.dbo.InfoTable ORDER BY IT_ID DESC 返回的并非是一张如您所愿的表，而是返回的一个游标。我们来看看什么是游标？SQL 是基于集合理论的，集合不会预先对它的行进行排序，它只是成员的逻辑集合，成员的顺序无关紧要，对表进行排序的查询可以返回一个对象，包含按照特定物理顺序组织的行，ANSI 把这种对象称为游标。

      正因为使用了 ORDER BY 子句的查询返回的是游标，从而使其不能用作表表达式(即：试图、内联表值函数、子查询、派生表等)，那么当您在 SQL Server 中运行如下派生表查询时会产生错误：

    SELECT *
      FROM (SELECT IT_ID, Title
            FROM reglogin.dbo.InfoTable
            ORDER BY IT_ID DESC) AS newTable;

      您是否已经猜到，比较两个 T-SQL 语句的差别，您应该可以得到这样一个结论：SQL Server 允许您在“表表达式”中使用 TOP 查询。实际上只有指定了 TOP 选项，才能在“表表达式”中使用带有 ORDER BY 子句的查询。换句话说，同时指定了 TOP 子句和 ORDER BY 子句的查询可以返回一个关系结果，而非游标。具有讽刺意味的是，使用了非标准不属于关系范畴的 TOP 选项后，本来应该返回游标的查询却返回一个关系结果。正因为如此，对这些非标准非关系特性的支持允许程序员以一种非常荒唐的方式利用它们，而事实上根本不应该支持这些方法。

      对此您是否有所触动呢？我们曾经如此做过，却也无碍，但是这样是否正确呢？聪明的你，或许有答案了。

     3. 关于 NULL 的一些琐事

      我依然喜欢在查询分析器中，手工编写一切和 SQL 有关的脚本，当然创建表的 SQL 语句也不例外，例如下面这个 SQL 语句：

USE D_Dowpol
IF EXISTS(SELECT name FROM sysobjects
      WHERE name = 'ProcurementPlan' AND type = 'U')
  DROP TABLE ProcurementPlan
GO
CREATE TABLE [dbo].[ProcurementPlan] -- 采购计划表
(
      AutoID           int IDENTITY(1,1) PRIMARY KEY,    -- 自增编号
      PlanID           nvarchar(200) UNIQUE DEFAULT CAST(GETDATE() AS nvarchar(20)) NOT NULL, -- 采购计划编号(DP_CG_20090520152412)
      CreateDate       datetime NOT NULL,      -- 选择的采购计划的日期(可选的？创建记录的日期?)
      ProcurementItem  nvarchar(500) NOT NULL, -- 采购项目(我的理解就是采购名目)
      SupplierID       int NOT NULL,           -- 供应商编号
      PlannedType      bit NOT NULL,           -- 计划类型 0:表示计划内；1:表示计划外
      PlannedFund      decimal(14,2) DEFAULT (0.00) NOT NULL,  -- 计划资金(所有金额都统一保留两位小数，必须填写)
      Dollar           decimal(14,2) DEFAULT (0.00) NOT NULL,  -- 对应美元金额(所有金额都统一保留两位小数，必须填写)
      Remark           nvarchar(2000) DEFAULT '无' NOT NULL,  -- 特殊情况备注
      EnteringEmp      nvarchar(20) NOT NULL,      -- 录入员工(填写记录的人，思考一下是否要做外键关联？)
      Assessor         nvarchar(20) NOT NULL,      -- 审核人
      IsPassed         bit DEFAULT 0 NOT NULL,     -- 是否审核通过(0:还未审核通过；1:审核通过)
      IsDeleted        bit DEFAULT 1 NOT NULL      -- 是否已逻辑上删除(0:逻辑上删除；1:逻辑上数据有效)
)
GO
ALTER TABLE [dbo].[ProcurementPlan]
ADD CONSTRAINT FK_ProcurementPlan_SupplierID FOREIGN KEY (SupplierID)
REFERENCES Supplier (SupplierID)
GO
ALTER TABLE [dbo].[ProcurementPlan]
ADD CONSTRAINT FK_ProcurementPlan_EnteringEmp FOREIGN KEY (EnteringEmp)
REFERENCES T_Employee (NickName)
GO
ALTER TABLE [dbo].[ProcurementPlan]
ADD CONSTRAINT FK_ProcurementPlan_Assessor FOREIGN KEY (Assessor)
REFERENCES T_Employee (NickName)
GO

      您是否发现了什么，一些关于 NULL 的细节，是的，我在创建表的时候显示定义了 NOT NULL。一直以来，我都习惯于在任何情况下创建表时候显示声明字段的 NOT NULL 或者 NULL。这是个好习惯，我一直都这么觉得，可为什么是好习惯我却是最近才明了，请您听我细细道来。事实上，如果您在创建表时，不显示声明字段是否为 NULL，SQL Server 会假设其为 NOT NULL。

      您会说我的 SQL Server 默认的是允许字段为 NULL 值啊，没错，您的错觉来自于许多与 SQL Server 共同工作的工具或者界面都开启了一个会话设置，使默认为 NULL 值。我们当然也可以通过一个会话设置或数据库选项使 SQL Server 本身默认允许 NULL 值。如果在创建表时列定义中不显示声明 NULL 或者 NOT NULL，然后您把那些脚本放在一台与您之前使用过的服务器，有着不同默认设置的数据库服务器上执行，将得到相反的结果。

      您是否还发现，我实际上在创建 ProcurementPlan 表时，将其所有列均声明为 NOT NULL，并且在逻辑上本应该可以允许为 NULL 值的列上定义了默认值，这又是为什么呢？简单点说吧，要处理 NULL 值给存储引擎增加了复杂度，因为 SQL Server 在每条记录中都设置了一个特殊的 bitmap 来显示哪些允许 NULL 的列上存储的真的是空值。如果允许 NULL，在访问每一行的时候 SQL Server 都必须对这个 bitmap 进行解码。更进一步说，允许 NULL 值同时也增加了应用程序的复杂度，因为总是要编写一些特殊的逻辑来处理空值的情况。

       因为 NULL 值的一些微妙之处，我遵从了 Kalen Delaney 导师给出的意见：？可能的话，将表中所有的列都显示的声明为 NOT NULL，并且为丢失的或未知的项定义默认值。说真的，我觉得这个建议非常的棒，在实际运用过程中，给我带来很多意想不到的思路，例如下面我将描述的最后一个话题。

     4. 如何利用 T-SQL 编程满足一个苛刻的需求

      有一天，老板对你说：我需要每条记录的编号是按照指定的格式生成的，并且具有唯一性，而不只是单纯连续的 1，2，3，4……这些数字，明白了吗？

      您还记得上面那张表吗？对，里面有一个 PlanID 列，这个字段是用来描述采购计划编号的，老板告诉我，最理想的情况就是按照 DP-P-YYMMXXXXX 的格式生成，具体点就是：例如现在是 6 月份如果填写这张表的记录，理应生成 DP-P-090600001，DP-P-090600002，DP-P-090600003……这样的记录，并且需要满足多人操作，也就是能够满足良好的并发操作。

      好了，来看看我的初步解决方案是如何满足这样的需求的：

------------------------ 同步机制生成序列值的存储过程 ------------------------
USE D_Dowpol;
GO
IF OBJECT_ID('[dbo].[SyncSeq]') IS NOT NULL
DROP TABLE [dbo].[SyncSeq];
GO
CREATE TABLE [dbo].[SyncSeq] (val INT);
INSERT INTO [dbo].[SyncSeq] VALUES(0);

IF OBJECT_ID('[dbo].[usp_SyncSeq]') IS NOT NULL
   DROP PROC [dbo].[usp_SyncSeq];
GO
CREATE PROC [dbo].[usp_SyncSeq]
(
   @val AS INT  OUTPUT
)
AS
   UPDATE [dbo].[SyncSeq]
   SET @val = val = val + 1;      -- T-SQL 专有语法，等价于 SET val = val + 1, @val = val + 1

                                  -- SQL Server 会将先排它地锁定该行并递增val，获取该值，事务完成后释放该锁
GO

USE D_Dowpol
IF EXISTS (SELECT [name] FROM sysobjects
WHERE [name] = 'proc_AddProcurementPlanData' AND type = 'P')
DROP PROCEDURE proc_AddProcurementPlanData
GO
CREATE PROCEDURE [dbo].[proc_AddProcurementPlanData]
(
  @CreateDate   datetime,
  @ProcurementItem nvarchar(500),
  @SupplierID   int,
  @PlannedType  bit,
  @PlannedFund  decimal(14,2),
  @Dollar    decimal(14,2),
  @Remark    nvarchar(2000),
  @EnteringEmp  nvarchar(50),
  @Assessor   nvarchar(50)
)
AS
BEGIN
      SET NOCOUNT ON

      DECLARE @curDay AS DATETIME,
              @upd_rowcount int,
              @ins_error int,
              @IDENTITY AS INT,
              @Key AS INT,
              @ID nvarchar(50),
              @Count AS INT,                      ---- 用于统计每月1号插入的数据的条数
              @PlanID nvarchar(100)
      -- 防止“备注”中只输入一堆空格字符进入数据库。
      SELECT @Remark = REPLACE(@Remark, ' ', ''),
             @PlanID = 'DP_P_' + CONVERT(nvarchar(4), GETDATE(), 12),
             @curDay = DAY(GETDATE())

      BEGIN TRAN AddPlanTran  ---- 事务开始 ----

      SELECT @Count  = (SELECT COUNT(AutoID)
                           FROM D_Dowpol.dbo.ProcurementPlan
                              WHERE PlanID LIKE @PlanID + '%')

       IF @Remark IS NULL OR (DATALENGTH(@Remark) = 0)
       BEGIN
         INSERT INTO D_Dowpol.dbo.ProcurementPlan
            (CreateDate, ProcurementItem, SupplierID, PlannedType, PlannedFund, Dollar, EnteringEmp, Assessor)
         VALUES
            (@CreateDate, @ProcurementItem, @SupplierID, @PlannedType, @PlannedFund, @Dollar, @EnteringEmp, @Assessor)
       END
       ELSE
       BEGIN
         INSERT INTO D_Dowpol.dbo.ProcurementPlan
            (CreateDate, ProcurementItem, SupplierID, PlannedType, PlannedFund, Dollar, Remark, EnteringEmp, Assessor)
       VALUES
            (@CreateDate, @ProcurementItem, @SupplierID, @PlannedType, @PlannedFund, @Dollar, @Remark, @EnteringEmp, @Assessor)
       END

       SELECT @ins_error = @@ERROR

       IF @ins_error = 0
       BEGIN
      

           SELECT @IDENTITY = IDENT_CURRENT('ProcurementPlan')
            /*
             还存在一个缺陷，就是假设服务器上正确的时间是8月份，忽然因为出现一些异常，有人把服务器上的
             时间改成了7月，而且七月的 DP-P-09070018，然后实际上序列生成器中的序列值在8月份没有出现异
             常的时候已经生存到9，显然如果这个时候有人要插入数据进入数据库，会出现错误，因为此刻生成的
             PlanID为：DP-P-09070009，而这个编号已经在7月份有人插入数据时生成了，所以对设置了唯一约束
             的PlanID字段来说，就会报错了。如何解决这个问题呢？
             我的初步想法就是，每次进来之前都根据 @Count的值与 D_Dowpol.dbo.SyncSeq 表中的值做判断，如果
             不相符的话，那就首先将 D_Dowpol.dbo.SyncSeq 的值修改为 @Count 后再执行其他逻辑即可。
            */
           IF @curDay = 1 AND @Count = 0 -- 每个月1号之后，在没有插入任何新数据之前，把序列生成器首先归零，每个月仅此一次
           BEGIN
               UPDATE D_Dowpol.dbo.SyncSeq
               SET val = 0
           END

           EXEC D_Dowpol.dbo.usp_SyncSeq @Key OUTPUT  -- 获取同步序列生成器生成的序列值

            IF @Key BETWEEN 0 AND 9
              SELECT @ID = '000' + CAST(@Key AS nvarchar(10))
            ELSE IF @Key BETWEEN 10 AND 99
              SELECT @ID = '00' + CAST(@Key AS nvarchar(10))
            ELSE IF @Key BETWEEN 100 AND 999
              SELECT @ID = '0' + CAST(@Key AS nvarchar(10))
            ELSE
              SELECT @ID = CAST(@Key AS nvarchar(10))

            UPDATE D_Dowpol.dbo.ProcurementPlan
               SET PlanID = @PlanID + @ID
                  WHERE AutoID = @IDENTITY

            SELECT @upd_rowcount = @@ROWCOUNT

            IF @upd_rowcount > 0
            BEGIN
                SELECT 1 AS Result   -- 返回1表示唯一的成功标识？
                COMMIT TRAN AddPlanTran
            END
            ELSE
            BEGIN
                SELECT 2 AS Result   -- 返回2表示在修改 D_Dowpol.dbo.ProcurementPlan 表的 PlanID 字段时出现错误并且回滚
                ROLLBACK TRAN AddPlanTran
            END
       END
       ELSE
       BEGIN
           SELECT 3 AS Result        -- 返回3表示在向 D_Dowpol.dbo.ProcurementPlan 表插入数据时就出现了错误并且回滚
           ROLLBACK TRAN AddPlanTran
       END
END
GO

      如果您认真看过上面一大段的 T-SQL 代码，剔除那些无用的部分，您定会发现感兴趣的关键代码，呵呵。没错，有同仁会发现上面的 T-SQL 代码都是在 SQL Server 2000 编写的，从获取 @@ERROR 处理错误机制的方式上看来，有着非常浓郁的 SQL2000 的风格味道，没有 SQL2005 处理错误机制那么灵活和华丽。同时，我大量使用了非 ANSI 的 SELECT 赋值，尤其是在给多个变量赋值时，没有采取 SET 赋值的方式。其实，在正确获取 @@ERROR 和 @@ROWCOUNT 值时候，如果仅仅只是声明两个变量 @error 和 @rowcount，然后利用 SET 分别将全局变量赋值给局部变量的话，您将无法获取正确的 @rowcount 值，因为 SET @error = @@ERROR 就会产生一条影响行数的记录。故此，要想在 ANSI SQL 标准下获得正确的 @rowcount 值不是看起来那么简单的事情。但是如果您使用 T-SQL 语法的话，简单的使用 SELECT 给多个变量赋值的特点的话，一句话就能解决这个问题：SELECT @rowcount = @@ROWCOUNT, @error = @@ERROR;。

      从我编写上面较长的存储过程时候，就不停告诉过自己，如果只是想在插入数据那一刻就要生存满足业务规则的编号，会将把自己置于一个窘迫的禁地，有时我们真的需要退一步，让自己追寻那海阔天空的感觉，这未尝不是件好事，我就是在一退中找到了进的感觉。当然，在解决这个按照指定规则生成连续编号的问题时，我所给出的解决方案不一定就是最优的方案，希望能够得到大家积极回复和探讨。

      其实，关于 SQL Server 和 T-SQL 的话题实在太多太多，自己能力有限，想表达的东西总是落笔顿塞，只能一点一点挤牙膏似的记录下来，与大家共同商讨学习，停笔之时，已经是凌晨5点，毫无倦意，心中已酝酿下一篇关于 T-SQL 的小九九了。