Transact-SQL程式設計規範

1.  概述

1.1. 基本原則

以大小寫敏感編寫SQL語句。

盡量使用Unicode 資料類型。

優先使用連接配接代替子查詢或嵌套查詢。

盡量使用參數化SQL查詢代替語句拼接SQL查詢。

禁止使用[拼音]+[英語]的方式來命名SQL對象或變量。

盡量使用存儲過程代替SQL語句。

1.2. 基本規範

建議采用Pascal樣式或Camel樣式命名資料庫對象。

大寫T-SQL語言的所有關鍵字，謂詞和系統函數。

2.  命名規範

在一般情況下，采用Pascal樣式或Camel樣式命名資料庫對象，使在開發基于資料庫應用程式的時候通過ORM工具生成的資料通路代碼不需要調整就符合程式開發語言（比如C#）命名規範。另外，關系型資料庫同Xml結合得越來越緊密,規範的命名越來越重要。

在實際資料庫開發過程中，如果需求方已經提供資料庫設計方案，建議以提供的方案為準；在原有資料庫上進行更新開發時，在可行的情況下可适當做出設計調整以符合程式設計規範。

1.3. 對象命名

1.3.1.  資料庫

第一種方式，采用Pascal樣式命名，命名格式為[項目英文名稱]。

示例：AdventureWorks

第二種方式，采用Pascal樣式命名，命名格式為[項目英文名稱]+ Db。

示例：AdventureWorksDb

  BizTalkRuleEngineDb

建議采用第一種方式。

1.3.2.  資料庫檔案

資料檔案：[資料庫名稱]+ _Data.mdf

日志檔案：[資料庫名稱]+ _Log.ldf

示例：AdventureWorks_Data.mdf

      AdventureWorks_Log.ldf

1.3.3.  關系型資料倉庫

采用Pascal樣式命名，命名格式為[項目英文名稱]+ DW。

示例：AdventureWorksDW

1.3.4.  資料架構

除SQL Server 系統定義的資料架構外，建立架構采用Pascal樣式命名，命名格式為[架構名]。

示例：HumanResources

      Production

對資料庫對象 Table，View，Procedure，Function等使用資料架構進行歸類。在SQL Server 2000中dbo為預設架構。

1.3.5.  資料表

采用Pascal樣式命名，命名格式為[表名]。

示例：Employee

      Product

表名以英文單數命名，主要是參考SQL Server 2005示例資料庫，個人了解不采用複數是為了更好的使用ORM工具生成符合程式設計規範的代碼（比如C#）。

示例：使用Product

  而不是Products

1.3.6.  資料視圖

視圖名稱采用Pascal樣式命名，命名格式為v +[視圖名稱]。

示例：vEmployee

      vSalesPerson

1.3.7.  資料列

列名稱命名采用英文單詞或縮寫，英文單詞隻來自于具體業務定義，盡量表達清楚含義。采用Pascal樣式命名，命名格式為[列名稱]。

示例：AddressID

      PostalCode

盡量避免使用拼音命名，如果不可避免，對于比較短的列名，采用拼音全寫，如果拼音列名比較複雜，可以采用首個字用全拼，其它字用首字母大寫表示。

示例：甯波 Ningbo

  經營方式 JingYFS

1.3.8.  存儲過程

建議采用Pascal樣式命名，命名格式為[存儲過程名稱]。

示例：GetUser

     AddUser

備注：在SQL Server 2005示例資料庫中使用Camel樣式命名。

1.3.9.  函數

自定義函數采用Pascal樣式命名，命名格式為[函數名]，系統函數使用全部大寫。

示例：SELECTISNULL(@LastName,'Unknown last name');

GETDATE()

1.3.10.     使用者定義資料類型

采用Pascal樣式命名，命名格式為[自定義資料類型名稱]。

示例：Flag

      NameStyle

1.3.11.     DML觸發器

DML觸發器是當資料庫伺服器中發生資料操作語言 (DML) 事件時要執行的操作。DML 事件包括對表或視圖發出的 UPDATE、INSERT 或 DELETE 語句。根據事件不同命名規則使用字首進行區分,格式為 [u|i|d]+[表名|視圖名]

示例：uEmployee 

  iEmployee

      dEmployee

另外一種方式為，

AFTER 觸發器：TR_表名_[後面插入加I,修改加U,删除加D]。

INSTEAD OF 觸發器：TR_表名或視圖名_OF[後面插入加I,修改加U,删除加D]

1.3.12.     DDL觸發器

響應各種資料定義語言 (DDL) 事件而激發。這些事件主要與以關鍵字 CREATE、ALTER 和 DROP 開頭的 Transact-SQL 語句對應。執行 DDL 式操作的系統存儲過程也可以激發 DDL 觸發器。

采用Camel樣式命名，命名單詞能夠描述DDL觸發器功能。

示例：

CREATETRIGGER safety 

ONDATABASE 

FOR DROP_TABLE, ALTER_TABLE 

AS 

   PRINT'You must disable Trigger "safety" to drop or alter tables!' 

   ROLLBACK ;

另外一種方式為添加ddl字首，

CREATETRIGGER[ddlDatabaseTriggerLog] 

FOR DDL_DATABASE_LEVEL_EVENTS 

AS

1.3.13.     主鍵、外鍵關系和索引

主鍵: PK_[表名稱]_[主鍵]；如果是組合主鍵，使用PK_[表名]_[主鍵1]_[主鍵2]。

示例：PK_Store_CustomerID

  PK_StoreContact_CustomerID_ContactID

外鍵關系：FK_[從表名稱]_[主表名稱]_[外鍵列名稱]。

示例：FK_StoreContact_Store_CustomerID

聚集索引：PK_[表名稱]_[主鍵]；如果是組合主鍵，使用PK_[表名]_[主鍵1]_[主鍵2]。

示例：PK_Store_CustomerID 

唯一非聚集索引:AK_[表名稱]_[列名稱]。

示例：AK_Store_rowguid

不唯一非聚集索引：PK_[表名稱]_[列名稱]。

示例：IX_Store_SalesPersonID

主 XML索引：PXML_[表名稱]_[Xml類型列名稱]。

示例：PXML_Store_Demographics

備注：以上命名參考Sql Server 2005示例資料庫，一般隻需設計器自動生成，不需要額外修改。

1.4. 參數命名

1.4.1.  資料列參數

命名格式為 @ +[列名稱]。

示例：@EmployeeID

在列名不符合Pascal樣式時（早期遺留系統），例如使用全部大寫的列名稱，或使用“_”進行連接配接的字段名稱，參數名稱定義使用 @ +[列名稱]，這裡的列名稱盡量符合Pascal樣式命名。

1.4.2.  非資料列參數

在參數無法跟列名稱進行關聯時，使用能夠反映該參數功能的英文單詞或單詞組合, 采用Pascal樣式命名。

示例：@ErrorID

      @Flag

1.5. 常見命名

1.5.1.  常用字段命名

這裡的常用字段是指在建表時頻繁使用的表名或列名，下表對常用字段進行建議性定義,

列名稱          資料類型             說明

CreatedDate     datetime                紀錄建立日期，一般使用GETDATE()自動生成

ModifiedDate        datetime                紀錄最後修改日期，首次使用GETDATE()

DeletedDate     datetime                記錄删除（标記删除）日期

StartDate       datetime                開始日期

EndDate         datetime                結束日期

StartTime       datetime                開始時間

EndTime         datetime                結束時間

rowguid         uniqueidentifier        唯一辨別行的ROWGUIDCOL号，用于支援合并複制

ID              int                 使用ID代替Id或id。一般為自增長主鍵列

ParentID            int                 父ID

Status          int                 狀态

3.  SQL編寫

3.1. 大小寫

大寫T-SQL 語言的所有關鍵字，謂詞和系統函數。變量名稱及遊标名稱使用Pascal樣式。資料類型定義使用全部小寫。

示例：DECLARE@LastNamenvarchar(32);

3.2. 使用“;”

使用“;”作為 Transact-SQL 語句終止符。雖然分号不是必需的，但使用它是一種好的習慣。

USE AdventureWorks;

GO

DECLARE@findvarchar(30);

SET@find='Man%';

SELECT LastName, FirstName, Phone

FROM Person.Contact

WHERE LastName LIKE@find;

3.3. 存儲格式

盡量采用Unicode資料存儲格式，提高可移植性和相容性，實際應用中盡量使用nchar、nvarchar、ntext代替char、varchar、text。

3.4. 類型選擇

如果字元具有明确的長度，使用nchar代替nvarchar；char代替varchar。

在隻有兩個可能數值時，使用bit代替int或smallint。

在SQL Server 2005中，使用nvarchar(MAX)代替ntext；varchar(MAX)代替text；varbinary(MAX)代替image。

在特殊的資料表結構中可考慮xml資料類型，達到事半工倍的效果。

3.5. 預設值

在建立資料表時，盡量使用預設值代替NULL值。比如設定CreatedDate列預設值為GETDATE()。在可行的情況下設定字段為不允許空。

3.6. 字段長度

始終指定字元資料類型的長度，并確定允許使用者可能需要的最大字元數，避免超出最大長度時出現字元丢失現象。對于字元型資料，建議采用2的n次方來定義資料長度。

示例：nvarchar(32)

  varchar(64)

3.7. 使用“'”

在 T-SQL 代碼中為字元常量使用單引号,避免使用雙引号。

3.8. 語句縮進

一個嵌套代碼塊中的語句使用四個空格的縮進。使用Microsoft SQL Server Management Studio ,選擇“工具”菜單，打開“選項”菜單，在選項對話框中選擇文本編輯器->純文字->制表符,選中“插入空格單選框”,設定“制表符大小”為4，縮進大小為“4”。

3.9. 語句換行

建議SQL代碼每行以關鍵字或“'”開頭。

SELECT[ShiftID]

      ,[Name]

      ,[StartTime]

      ,[EndTime]

      ,[ModifiedDate]

  FROM[AdventureWorks].[HumanResources].[Shift]

3.10.   語句分割

使用一個（而不是兩個）空行分隔 T-SQL 代碼的邏輯塊。

3.11.   使用“*”

盡量避免在任何代碼中使用 “SELECT*”。

3.12.   表名别名

表名别名要簡短，但意義要盡量明确。通常使用大寫的表名作為别名，使用 AS 關鍵字指定表或字段的别名。

3.13.   類型轉換

不要依賴任何隐式的資料類型轉換，不要假定 T-SQL 會進行必要的轉換。例如，把數字變量賦予字元值。相反，在為變量指派或比較值之前，應使用适當的 CONVERT 函數使資料類型相比對。

3.14.   數值比較

不要将空的變量值直接與比較運算符（符号）比較。如果變量可能為空，應使用 ISNULL 或 ISNOTNULL 進行比較，或者使用 ISNULL 函數。

3.15.   排序

決不要依賴 SELECT 語句會按任何特定順序傳回行，除非在 ORDERBY 子句中指定了順序。通常，應将 ORDERBY 子句與 SELECT 語句一起使用。可預知的順序（即使不是最友善的）比不可預知的順序強，尤其是在開發或調試過程中。在傳回行的順序無關緊要的情況下，可以忽略 ORDERBY ，減少資源開銷。

3.16.   Unicode字元串

在Unicode字元前面使用N字首，避免引起資料的不一緻。

-- Assumes the default code page is not Greek

CREATETABLE #t1 (c1 nchar(1))

INSERT #t1 VALUES(N'Ω')

INSERT #t1 VALUES('Ω')

SELECT*FROM #t1

輸出結果：

c1   

---- 

Ω

O

3.17.   BEGIN...END 塊

在SQL代碼快中盡量使用BEGIN...END 語句塊，提高代碼可閱讀性。

3.18.   TRY塊

在SQL Server 2005中對一些可能執行失敗的語句盡量使用TRY塊。Transact-SQL 語句組可以包含在 TRY 塊中，如果 TRY 塊内部發生錯誤，則會将控制傳遞給 CATCH 塊中包含的另一個語句組。

BEGIN TRY

    SQL 語句組1

END TRY

BEGIN CATCH

    SQL 語句組2

END CATCH;

3.19.   TOP子句

在SQL Server 2005中加強了TOP的使用，盡量使用TOP(變量)來減少SQL拼串現象。

3.20.   TRANSACTION編寫

隻要在例程中使用多個資料庫修改語句，包括在一個循環中多次執行一個語句，就應考慮聲明顯式事務。在SQL SERVER 2005 中，增加了TRY塊可進行很好的應用。

執行個體：

    BEGIN TRY

        BEGINTRANSACTION;

        UPDATE[HumanResources].[Employee] 

        SET[Title]=@Title 

            ,[HireDate]=@HireDate 

            ,[CurrentFlag]=@CurrentFlag 

        WHERE[EmployeeID]=@EmployeeID;

        INSERTINTO[HumanResources].[EmployeePayHistory] 

            ([EmployeeID]

            ,[RateChangeDate]

            ,[Rate]

            ,[PayFrequency]) 

        VALUES (@EmployeeID, @RateChangeDate, @Rate, @PayFrequency);

        COMMITTRANSACTION;

    END TRY

    BEGIN CATCH

        -- Rollback any active or uncommittable transactions before

        -- inserting information in the ErrorLog

        IF@@TRANCOUNT>0

        BEGIN

            ROLLBACKTRANSACTION;

        END

        EXECUTE[dbo].[uspLogError];

    END CATCH;

3.21.   存儲過程

在編寫存儲過程時，使用PROCEDURE 代替 PROC 簡寫。

示例：CREATEPROCEDURE[dbo].[存儲過程名字]

4.  代碼注釋

4.1. 代碼頭部注釋

在SQL代碼塊（sql檔案或存儲過程）的頭部進行注釋，标注建立人(Author)、創始日期(Create date)、修改資訊(Modify [n])。

格式：

-- =============================================

-- Author:      <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- Modify [n]:  < Modifier,Date, Description >

-- ================================================

-- Author:      Zhanghaifeng

-- Create date: 2006-12-25

-- Description: H2000報關單回執處理

-- Modify [1]:  鄭佐, 2006-12-31, 簡化邏輯判斷流程

-- Modify [2]:  鄭佐, 2007-01-20, 更新條件判斷

注：日期格式使用 yyyy-MM-dd。Modify [n] n代表修改序号，從1開始，每次修改加1。

4.2. TRANSACTION注釋

建議在每個事務的開頭進行注釋，說明該事務的功能。

-- < Modifier,Date, Description >

BEGINTRANSACTION;

5.  附錄A 命名規則

常見命名規則有四種樣式：完全大寫、完全小寫、Pascal 大小寫和 Camel 大小寫。

5.1. Pascal 大小寫

組成辨別符的每個單詞的首字母大寫，其餘字母小寫的書寫約定。對于縮寫的雙字母單詞，要求全部大寫。

例如：ApplicationException

      ID

5.2. Camel 大小寫

辨別符的首字母小寫，每個後面連接配接的單詞的首字母大寫，其餘字母小寫的書寫約定。對于縮寫的雙字母單詞，要求它們出現在辨別符首部時全部小寫，否則全部大寫。

例如：applicationException

      id

5.3. 匈牙利命名法

匈牙利命名法由匈牙利程式員發明，他在微軟工作了多年，此命名法就是通過微軟的各種産品和文檔傳出來。多數有經驗的程式員，不管他們用的是哪門語言，都或多或少在使用它。

基本原則：變量名 = 屬性 ＋ 類型 ＋ 對象描述

即一個變量名是由三部分資訊組成，這樣，程式員很容易了解變量的類型、用途，而且便于記憶。

本文轉自麒麟部落格園部落格，原文連結：http://www.cnblogs.com/zhuqil/archive/2009/11/19/1606001.html，如需轉載請自行聯系原作者