T-SQL编程规范.doc

Transact-SQL編程規範 1. 基本原則以大小寫敏感編寫SQL語句。儘量使用Unicode 資料類型。優先使用連接代替子查詢或嵌套查詢。儘量使用參數化SQL查詢代替語句拼接SQL查詢。禁止使用拼音+英語的方式來命名SQL物件或變數。2. 命名規範在一般情況下，採用Pascal樣式或Camel樣式命名資料庫物件，使在開發基於資料庫應用程式的時候通過ORM工具生成的資料訪問代碼（比如C#）更好的符合命名規範。另外，關係型數據庫同Xml結合得越來越緊密,規範的命名很有必要。在實際資料庫開發過程中，如果需求方已經提供資料庫設計方案，以提供的方案為准；在原有資料庫上進行升級開發時，在可行的情況下可適當做出設計調整以符合編程規範。1.1. 對象命名1.1.1. 資料庫第一種方式，採用Pascal樣式命名，命名格式為項目英文名稱。示例：AdventureWorks第二種方式，採用Pascal樣式命名，命名格式為項目英文名稱 + Db。示例：AdventureWorksDb BizTalkRuleEngineDb建議採用第一種方式。1.1.2.數据庫使用大寫名字。1.1.3. 數據架構除SQL Server 系統定義的資料架構外，新建架構採用Pascal樣式命名，命名格式為架構名。示例：HumanResources Production對資料庫物件 Table，View，Procedure，Function等使用資料架構進行歸類。在SQL Server 2000中dbo為默認架構。1.1.4. 數據表採用Pascal樣式命名，命名格式為表名。示例：Employee Product表名以英文單數命名，主要是參考SQL Server 2005示例資料庫，個人理解為更好的使用ORM工具生成符合編程規範的代碼（比如C#）。典型的例子，使用Product ,而不是Products。1.1.5. 資料視圖視圖名稱採用Pascal樣式命名，命名格式為Vew + 視圖名稱。示例：VewEmployee VewSalesPerson1.1.6. 數據列列名稱命名採用英文單詞或縮寫，英文單詞只來自于具體業務定義，儘量表達清楚含義。採用Pascal樣式命名，命名格式為列名稱。示例：AddressID PostalCode儘量避免使用拼音命名，如果不可避免，對於比較短的列名，採用拼音全寫，如果拼音列名比較複雜，可以採用首個字用全拼，其他字用首字母大寫表示。示例：寧波 Ningbo經營方式 JingYFS1.1.7. 存儲過程建議採用Pascal樣式命名，命名格式為存儲過程名稱。示例：USP_GetUser備註：在SQL Server 2005示例資料庫中使用Camel樣式命名。如果存在嵌套調用情況避免使用#t1,#t2等臨時表名應用存儲過程名加編號的方式如#SP_GetUser_t11.1.8. 函數自定義函數採用Pascal樣式命名，命名格式為函數名，系統函數使用全部大寫或者全部小寫。示例：SELECT ISNULL(LastName,Unknown last name);GETDATE()1.1.9. 用戶定義資料類型採用Pascal樣式命名，命名格式為自定義資料類型名稱。示例：Flag NameStyle1.1.10. DML觸發器DML 觸發器是當資料庫伺服器中發生資料操作語言 (DML) 事件時要執行的操作。DML 事件包括對表或視圖發出的 UPDATE、INSERT 或 DELETE 語句。根據事件不同命名規則使用首碼進行區分,格式為 u|i|d + 表名|視圖名示例：Employee_UPD Employee_INS Employee_DEL如果存在嵌套調用情況避免使用#t1,#t2等臨時表名應用存儲過程名加編號的方式如# Employee_UPD_t11.1.11. DDL觸發器回應各種資料定義語言 (DDL) 事件而激發。這些事件主要與以關鍵字 CREATE、ALTER 和 DROP 開頭的 Transact-SQL 語句對應。執行 DDL 式操作的系統存儲過程也可以激發 DDL 觸發器。示例：CREATE TRIGGER ddlDatabaseTriggerLog ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS1.1.12. 主鍵、外鍵關係和索引除非必可避免否則都應使用外鍵以保証數据的完整性主鍵: PK_表名稱_主鍵；如果是組合主鍵，使用PK_表名_主鍵1_主鍵2。示例：PK_Store_CustomerID PK_StoreContact_CustomerID_ContactID外鍵關係：FK_從表名稱_主表名稱_外鍵列名稱。示例：FK_StoreContact_Store_CustomerID聚集索引：PK_表名稱_主鍵；如果是組合主鍵，使用PK_表名_主鍵1_主鍵2。示例：PK_Store_CustomerID PK_StoreContact_CustomerID_ContactID唯一非聚集索引:AK_表名稱_列名稱。示例：AK_Store_rowguid不唯一非聚集索引：PK_表名稱_列名稱。示例：IX_Store_SalesPersonID主 XML索引：PXML_表名稱_Xml類型列名稱。示例：PXML_Store_Demographics備註：以上命名參考Sql Server 2005示例資料庫，一般只需設計器自動生成，不需要額外修改。1.2. 參數命名1.2.1. 資料列參數命名格式為 + 列名稱。示例：EmployeeID在列名不符合Pascal樣式時（早期遺留系統），例如使用全部大寫的列名稱，或使用“_”進行連接的欄位名稱，參數名稱定義使用 + 列名稱。1.2.2. 非資料列參數在參數無法跟列名稱進行關聯時，使用能夠反映該參數功能的英文單詞或單詞組合, 採用Pascal樣式命名。示例：ErrorID Flag1.3. 常用字段命名這裏的常用字段是指在建表時頻繁使用的表名或列名，下表對常用字段進行建議性定義,列名稱資料類型說明CreationDatedatetime紀錄創建日期，一般使用GetDate()自動生成。LastDatedatetime紀錄最後修改日期，首次使用GetDate()rowguiduniqueidentifier唯一標識行的 ROWGUIDCOL 號，用於支持合併複製示例。IDint使用ID代替Id或id。一般為自增長主鍵列。3. SQL編寫規范編寫語句不寫非標准語句如*=left join語句之類重要部分需加入備注3.1. 大小寫大寫所有的 T-SQL 關鍵字，包括 T-SQL 函數。變數名稱及游標名稱使用Pascal樣式。資料類型定義使用全部大寫或者小寫。示例：DECLARE LastName NVARCHAR(32);3.2. 使用“;”使用“;”作為 Transact-SQL 語句終止符。雖然分號不是必需的，但使用它是一種好的習慣。示例：USE AdventureWorks;GODECLARE find VARCHAR(30);SET find = Man%;SELECT LastName, FirstName, PhoneFROM Person.ContactWHERE LastName LIKE find;3.3. 存儲格式儘量採用Unicode資料存儲格式，提高可攜性和相容性，實際應用中使用nchar、nvarchar、ntext代替char、varchar、text。3.4. 類型選擇在只有兩個可能數值時，使用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.Shift3.10. 語句分割使用一個（而不是兩個）空行分隔 T-SQL 代碼的邏輯塊。3.11. 使用“*”儘量避免在使用 “SELECT *”。3.12. 表名別名表名別名要簡短，但意義要儘量明確。通常使用大寫的表名作為別名，使用 AS 關鍵字指定表或欄位的別名。3.13. 類型轉換不要依賴任何隱式的資料類型轉換，不要假定 T-SQL 會進行必要的轉換。例如，而把數位變數賦予字元值。相反，在為變數賦值或比較值之前，應使用適當的 CONVERT 函數使資料類型相匹配。3.14. 數值比較不要將空的變數值直接與比較運算符（符號）比較。如果變數可能為空，應使用 IS NULL 或 IS NOT NULL 進行比較，或者使用 ISNULL 函數。3.15. 排序決不要依賴 SELECT 語句會按任何特定順序返回行，除非在 ORDER BY 子句中指定了順序。通常，應將 ORDER BY 子句與 SELECT 語句一起使用。可預知的順序（即使不是最方便的）比不可預知的順序強，尤其是在開發或調試過程中。在返回行的順序無關緊要的情況下，可以忽略 ORDER BY 的開銷。3.16. Unicode字串在Unicode字元前面使用N首碼，避免引起資料的不一致。示例：- Assumes the default code page is not GreekCREATE TABLE #t1 (c1 nchar(1)INSERT #t1 VALUES(N)INSERT #t1 VALUES()SELECT * FROM #t1輸出結果：c1 - O3.17. BEGIN.END 塊在SQL代碼快中儘量使用BEGIN.END 語句塊，提高代碼可閱讀性。3.18. TRY塊在SQL Server 2005中增加TRY塊，Transact-SQL 語句組可以包含在 TRY 塊中。如果 TRY 塊內部發生錯誤，則會將控制傳遞給 CATCH 塊中包含的另一個語句組。示例：BEGIN TRY SQL 語句組1END TRYBEGIN CATCH SQL 語句組2END CATCH;3.19. TOP子句在SQL Server 2005中加強了TOP的使用，儘量使用TOP(變數)來減少SQL拼串的使用。3.20. TRANSACTION編寫只要在常式中使用多個資料庫修改語句，包括在一個迴圈中多次執行一個語句，就應考慮聲明顯式事務。在SQL SERVER 2005 中，增加了TRY塊可進行很好的使用。實例： BEGIN TRY BEGIN TRANSACTION; UPDATE HumanResources.Employee SET Title = Title ,HireDate = HireDate ,CurrentFlag = CurrentFlag WHERE EmployeeID = EmployeeID; INSERT INTO HumanResources.EmployeePayHistory (EmployeeID ,RateChangeDate ,Rate ,PayFrequency) VALUES (EmployeeID, RateChangeDate, Rate, PayFrequency); COMMIT TRANSACTION; END TRY BEGIN CATCH - Rollback any active or uncommittable transactions before - inserting information in the ErrorLog IF TRANCOUNT 0 BEGIN ROLLBACK TRANSACTION; END EXECUTE dbo.uspLogError; END CATCH;3.21. 存儲過程在編寫存儲過程時，使用PROCEDURE 代替 PROC 簡寫。示例：CREATE PROCEDURE dbo.存儲過程名字4. 代碼注釋4.1. 代碼頭部注釋在SQL代碼塊（sql檔或存儲過程）的頭部進行注釋，標注創建人(Author)、創始日期(Create date)、修改資訊(Modify n)。格式：- =- Author: Author,Name- Create date: Create Da