SQL Server代码规范

6.1 对象创建脚本

使用数据库（USE）
不要在SQL Server源文件使用USE 数据库名语句,这会导致只能在此数据库创建对象,OSQL命令行工具可以在数据库中重定向.
删除对象:
IF Exists语句应该在Drop 对象语句之前执行:
IF object_id(‘tablename’) IS NOT NULL
   DROP TABLE TableName
 
表创建,索引,触发器
在开发过程中,所有的表创建脚本都可以合并为一个项目中的文档中(使用ERwin将会非常将会非常容易).此文档中每个表创建语句的后面应该有Go语句,当系统到达一个稳定点时,应该把文档中的从多个表按照表的不同分到单独的文件以利于维护.这此文档应该放到\TBL子目录下.
在每个创建单独表的文档中,都应该在创建表语句的前面使用if exists() drop table 语句.
每表创建单独的索引脚本文件,并把它们放到\TBL的子目录\INDEX下,命名为<表名>.SQL.在每个创建索引的语句之前,应该使用”if exists() drop…”语句,这样当此索引 已经存在时,它将会被先删除然后重新创建.
如果必要,创建单独的触发器脚本,并把它放置在\TBL的子目录 \TRG下.也命名为<表名>.SQL.
主键,外键,Check约束和默认值
所有的约束和默认值定义都应该直接通过ALTER TABLE命令来创建,在创建表的同时对约束和默认值进行嵌入式的定义应该被禁止,为了清晰起见.一个ALTER TABLE语句不应该包括多个对表的更改，我们推荐在每个更改语句之前使用“if exists() alter …. Drop onstraint…”，这样当此约束已经存在时，可以被重新创建。
主键定义有单独的脚本，并放置在\TBL的子目录\PK下。
外键定义也应该有独立的脚本，并放置与\TBL的子目录\FK下。
每个表的Check约束语句也应该放在一个单独的目录\TBL的子目录\CHECK之下。
表中列的默认值定义应该放在一个独立的脚本，并放置与TBL的子目录\DEFAULT之下。
在CREATE TABLE 中显式的为表的每一列定义是否为空，不要依靠数据库的默认设定，因为在数据库中这可能不同，从而出现了无意识的错误。
为索引和主键指定CLUSTERED或者NONCLUSTERED，不要依靠数据库的默认设定。
Grant 语句
.Grant语句应该独立与表，存储过程等的创建语句，为Grant语句创建单独的文件可以使我们很容易的重新对数据库对象进行授权。
  

6.2 数据库更改脚本

当在产品服务器上对数据库进行更改时,要非常小心和注意.这会避免产品服务库发生丢失数据或者停止服务等灾难的发生.
这个脚本应该是可以对数据库的对象进行判断的,比如,如果一个对象已经存在,那应该成功结束,而不是把此对象删除,因为此对象可能已经包括数据.比如表,如果要删除一个已经存在的表,则也删除了表中的数据.
当表或者视图重新被创建时,也要重新创建授权.
任何时候,当表被创建或者被重新创建时,确认所有的依靠关系,比如PK,FK,索引,触发器,默认值和视图都应该对重新创建.
当底层表结构更改时,视图都应该被重新创建.这个应该特别被注意,特别是视图是使用如”Select * from table”时.
如果一个表被复制,在SQL Server 2000使用sp_repladdcoumn和sp_ repldropcolumn存储过程.
脚本包括错误检测及事务的回滚.当把一个表分成两个表或者是多个复杂的轮换时,这个就很重要.
 在脚本中应该包括Raiserror脚本,这样会使以后的打错工作较为容易.理想情况下,应该首先使用Raiserror(‘msg’,0.1) WITH NOWAIT，因为这样当它被执行时会被送到客户端。
DECLARE @rows int, @rc int
SET NOCOUNT ON
IF NOT EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS
                        WHERE COLUMN_NAME = 'AgreementDurationID' AND
                                       TABLE_NAME = 'BudgetSellIn')
BEGIN
   IF EXISTS (SELECT * FROM sysobjects
                    WHERE name ='BudgetSellIn' AND xtype = 'u' AND
                    replinfo in (1,3))
   BEGIN
      EXEC @rc = sp_repladdcolumn 
                              @source_object = 'BudgetSellIn'
                             ,@column = 'AgreementDurationID'
                          ,@typetext = 'int NOT NULL CONSTRAINT DF_BudgetSellIn_AgreementDuID DEFAULT 0'
      IF @rc <> 0
      BEGIN
            RAISERROR('Failed adding column AgreementDurationID to BudgetSellIn....', 0,1) WITH NOWAIT
            GOTO CRASH
      END
      -- Drop the default constraint
      ALTER TABLE BudgetSellIn
      DROP CONSTRAINT DF_BudgetSellIn_AgreementDuID
      IF @@ERROR <> 0
      BEGIN
           RAISERROR('Failed droping DEFAULT CONSTRAINT DF_BudgetSellIn_AgreementDuID from BudgetSellIn....', 0,1) WITH NOWAIT
           GOTO CRASH
      END
 
      RAISERROR('Successfully added column AgreementDurationID to BudgetSellIn..',0,1) WITH NOWAIT 
   END
END
ELSE
RAISERROR('New Column AgreementDurationID already exists in BudgetSellIn..',0,1) WITH NOWAIT  
IF NOT EXISTS(SELECT * FROM sysobjects
           WHERE name = 'BudgetSellIn_FK_AgreementDuration' and type = 'f')          
BEGIN
   ALTER TABLE BudgetSellIn
   ADD CONSTRAINT BudgetSellIn_FK_AgreementDuration FOREIGN KEY
   (AgreementDurationID) REFERENCES AgreementDuration(AgreementDurationID)
  
   IF @@ERROR <>0
   BEGIN
        RAISERROR('Failed Adding FOREIGN KEY To AgreementDuration....', 0,1) WITH NOWAIT
        GOTO CRASH
   END
   RAISERROR('Successfully added FOREIGN KEY To AgreementDuration....', 0,1) WITH NOWAIT  
ELSE
     RAISERROR('FOREIGN KEY to AgreementDuration Already exists on Table BudgetSellIn...',0,1) WITH NOWAIT
END
GOTO VERYEND
CRASH:
RAISERROR ('Error Adding New Columns to BudgetSellIn...', 18,127)
VERYEND:
 

6.3 综合开发实践

       不要在存储过程中使用输入参数来更改它们的功能。这样会使程序中的耦合度大大增加。例如，一个存储过程根据输入参数向Customer表中插入记录或者Employee表中插入记录，这会使此存储过程复杂化和松耦合度，此时使用两个独立的存储过程（一个存储过程实现向Customer表插入记录和一个实现向Employee表插入记录）将会更好.
       不要使用关键字作为对象的标识.         
       使用ANSI风格的连接。不要使用老的T－SQL 外连接“*=”，这可能在将来的版本中不被支持.
       不要在存储过程中使用双引号，这是因为ANSI标准中默认就是QUOTED_IDENTIFIERS是设置为ON的，所以SQL Server将视任何双引号之内的字符为标识符.
       当调用存在参数的存储过程时，一个比较的好建议就是使用参数名和参数值来传递，而不是依次使用参数值来调用。
       为了清晰起见，it’s often a good practice to specifically include the correct parameters.  For example:
       当创建索引时，不管是否是cluster,都要明确的使用它
       当创建在一个具有Identity属性列的表时，应该包括起始数字和增加数字，也要包括是否为空。如:
            Create table Table1 (ColA int identity(1,1), colB varchar not null).
 
       我们都知道应该尽是避免硬编码，但是必要时，增加注释来说明硬编码的意义，例如：
 “where SalesChannelLevelID = 1 – SellInsales”
       当使用RPC调用一个存储过程时，在服务器名字上加上中括号，比如，一个服务器的名字可能叫“Fridge\Inst1”,如果不加中括号,将会出错.
INSERT INTO #CMODSSourcePROHistory
EXECUTE('['+@ServerName +'].CMODS.dbo.RSPGetSourcePROHistory')
 

6.4 文本文件格式

保持单行字符少于87个
每行SQL语句的宽度应该少于87个字符，如果太宽，我们在查看和打印时易读性会很差。
缩进间距
在T－SQL文件中，使用3个字符的缩进，我们可以在查询分析器中配置Tab键为3个空格的缩进（默认为8），如下示例所示：
IF EXISTS (SELECT ...
           FROM Table ...)
BEGIN
      statement 1
      statement 2
      IF (...)
      BEGIN
            statement 1
      END
END –- end of if table rows exist
 
SQL代码格式化
保证代码在项目范围内的一致性。为所有的代码段使用相同的格式和缩进方式.
大写所有的关键字，并且为SELECT,FROM,WHERE,GROUP BY,HAVING语句另起一行.为WHERE中的每一列另起一行，右对齐这些关键字。当WHERE语句包括子SELECT或者OR语句时，缩进它们，以使得比较容易的分清他们之前的依从关系（比如哪些语句是在一起的）
在复杂的连接中，缩进JOIN  和ON关键字，这会使SELECT，FROM和WHERE更突出，
排列CASE和相应的END语句。
我们大多喜欢在列表的左边使用逗号，因为这样在将来增加新的列时不用在最后的列后面加上逗号.同样这样也非常容易检查到是否有逗号没有写.
下面的例子显示一个复杂的WHERE  T－SQL语句语句：
SELECT
      Col1
     ,Col2
     ,Col3
     ,CASE WHEN t1.Col1 = 1 THEN ‘First’
               WHEN t1.Col1 = 2 THEN ‘Second’
               ELSE ‘Last’
     END AS Col4Name
FROM  Table1 t1
       JOIN  Table2 t2
         ON  t1.Col1 = t2.Col2
        AND  t2.Date = ‘1/1/1994’
 WHERE  (t2.Col4 = 3
          OR  (    t2.Col3 < 6
               AND t2.Col4 > 5
              )
        )
        AND NOT EXISTS
              (SELECT *
                 FROM  Table5 t5
                WHERE  t5.Col2 = t1.Col2)
GROUP BY...
HAVING ...
 
在IF和WHILE时，为BEGIN和END另起一行.
当在BEGIN和END中有很多行的T-SQL语句时，对END进行注释以使它们能够很好的匹配起来.
IF EXISTS (SELECT *
           FROM #tmptable)
BEGIN
      statement1
    statement2
END
ELSE
BEGIN
      statement1
      statement2
END
 
WHILE (sky = ‘blue’)
BEGIN
      statement1
      statement2
END –- end of while sky = ‘blue’
 
当进行插入操作时，应该给出列名
从来不应该依靠列的物理顺序来进行表的插入操作。应该在插入操作进行指定插入列的顺序，这将会使我们的维护和排错时更加容易.
INSERT Table1 (
      Column1
     ,Column2
     ,Column3
     ....)
SELECT
     Column1
     ,Column2
     ,Column3
     ...
FROM Table2
WHERE ....
OR
INSERT Table1
     (
      Column1
     ,Column2
     ,Column3
     ....)
VALUES (
      value1
     ,value2
     ,value3
      ....)
 
在SELECT列的列表中要使用表的别名
在Insert/Select 语句中，updates或者deletes操作可能要连接很多表，不管是否需要，我们推荐总是为每列加上表的别名。这将会防止当在连接的表中加入新列是出现错误“ambiguous column name”发生，同时这也能帮助维护人员列是来源于哪个表，试着使用表的别名来标识表是非常有用的，但是记住不要超过3
或者4个字符。
在Insert/Select语句，
INSERT Table1 (
    CountryCode
     ,SubsidiaryCode
     ....)
SELECT
     Ctry.CountryCode
     ,Sub.SubsidiaryCode
     ...
FROM Country ctry
JOIN Subsidiary sub
    ON ctry.SubsidiaryCode = sub.SubsidiaryCode
 
------------------------------------
-- For update statements, do not use a column alias
-- for the target column on the set statement.
-- To avoid ambiguouity, the table alias is listed after the UPDATE.
------------------------------------
UPDATE ctry
SET Countryname = ictry.CountryName
      ,SubsidiaryCode = ictry.SubsidiaryCode
  FROM Import..Country ictry
  JOIN Country ctry
    ON ictry.CountryCode = ctry.CountryCode
 
 
标题（header）
所有的文件都应该有一个标题，把文件头把在Create…AS语句之后，通过这种方法注释，其中也将包括更改历史，都将会存储在sysComments中，在用来和最近的Visual Sourcesafe中的版本进行比较时将会非常有用.
/******************************************************************
*name        :             --函数名/View/Store proc
*function    :              --函数功能
*input       :               --输入参数
*output      :               --输出参数
*author      :               --作者
*CreateDate  :             --创建时间
*UpdateDate  :           --函数更改信息（包括作者、时间、更改内容等）
*************************************************************************/
如：
IF EXISTS ( SELECT * FROM sysobjects
                  WHERE name = ' AddPublications ' AND type = 'P' )
BEGIN
      PRINT 'Dropping Procedure: AddPublications '
      DROP PROCEDURE AddPublications
END
  
PRINT 'Creating Procedure: AddPublications '
GO
CREATE PROCEDURE AddPublications
AS
/******************************************************************
** Desc:  The purpose of this procedure is to create the Publications
** for replication, as defined in the Publication table. 
** 
** Processing Steps:
**        1.  If database is not already published, publish it
**        2.  For each Publication in Publication table:
**              IF Publication doesn't exist
**                　Create Publication
**              IF SnapShot Job for Publication doesn't exist
**                  Create SnapShot Job
**        3.  Change LogReader Agent from default settings
**        4.  Change Checkup Agent Heartbeat from default settings
**
** Parameters: none
** Restart:
**        Restart at the beginning.  No code modifications required.
**
** Tables Used: 
**               Publication                                 　　　select
**               master..sysdatabases               　        select
**               Distribution..MSPublications                 select
**               Distribution..MSSnapShot_Agents         select
**               Distribution..MSLogReader_Agents            select
**               msdb..sysjobs                                         select
**               msdb..sysjobsteps                                   select
**              
** Return values:   = 0 Success
**                         < 0 Failure
**
** Called By:       None
**
** Calls:           sp_replicationdboption
**                   sp_addpublication
**                   sp_addpublication_snapshot
**                   sp_update_job
**                  sp_update_jobstep
**                  sp_update_jobschedule
**
** Author:          Michael Eldridge
** Date:             02/05/1998
********************************************************************************
** Change History
********************************************************************************
** Date        Author     Description
** ----------  ---------  -----------------------------------------------------
** 10/03/1998  jsuther    Sphinx agents now have step named 'Run Agent.' not
**                                  'Run Replication Agent.'
** 08/18/2000  brianell   Standardized
******************************************************************************/
SET NOCOUNT ON
SET ARITHABORT ON
 
注释
在注释中的“Change History”部分中包括更改历史.
在一个包括多行的注释中,双中划线应该被放在每行的开始,不要使用/* */风格的注释,因为注释中可能被嵌套.
不允许注释超过87个字符的宽度.
在注释中包括下面的信息将会很有帮助:谁申请了特定的更改,更改的时间,为了达到的商业目的的描述.
 
Comment complicated sections of code just above the section of code at the same indent level.
-----------------------------------------------------------------
-- Coment at level one
-----------------------------------------------------------------
WHILE (...)
BEGIN
      statement 1
      statement 2
   -------------------------------------------------------------
   -- Comment at level 2
   -------------------------------------------------------------
      statement 3
END
 

6.5 使用Return

使用Reture来标识存储过程,批处理的成功或者是失败.
>= 0 标识成功
<-99 标识失败 (SQL Server reserves -1 through -99).
当存储过程中包括很多语句时,Return一个明确的数字对于错误语句的定位来说是非常重要的.
IF @@error <> 0
BEGIN
     RAISERROR (‘ValidateEndDate 50001:  Invalid End Date’),18,127
     RETURN -200
END
 
输出参数可以被用来返回值.
EXEC @rc = ValidateReportEndDate @SourceID, @FiscalPeriodID output
 
所以有存储过程应该用Return来标识是否有错误发生.即使发生错误的可能性不在在(比如一个查询中只包括SELECT语句),也应该使用Return 0来标识成功执行.有效的使用Return将能很好的保证数据的一致性.
Select 语句可以返回一个或者多个行集.不要使用”SELECT *  FROM Customer”，因为如果在Customer表中新增了一列，应用程序有可能没有对此列进行处理的操作，从而有可能产生错误，并且“SELECT *  FROM Customer”对于数据库性能来说有很多负面影响。