T-SQL编程规范

Transact-SQL編程規範1.基本原則以大小寫敏感編寫SQL語句。儘量使用Unicode資料類型。優先使用連接代替子查詢或嵌套查詢。儘量使用參數化SQL查詢代替語句拼接SQL查詢。禁止使用[拼音]+[英語]的方式來命名SQL物件或變數。2.命名規範在一般情況下，採用Pascal樣式或Camel樣式命名資料庫物件，使在開發基於資料庫應用程式的時候通過ORM工具生成的資料訪問代碼（比如C#）更好的符合命名規範。另外，關係型數據庫同Xml結合得越來越緊密,規範的命名很有必要。在實際資料庫開發過程中，如果需求方已經提供資料庫設計方案，以提供的方案為准；在原有資料庫上進行升級開發時，在可行的情況下可適當做出設計調整以符合編程規範。1.1.對象命名1.1.1.資料庫第一種方式，採用Pascal樣式命名，命名格式為[項目英文名稱]。示例：AdventureWorks第二種方式，採用Pascal樣式命名，命名格式為[項目英文名稱]+Db。示例：AdventureWorksDbBizTalkRuleEngineDb建議採用第一種方式。1.1.2.數据庫使用大寫名字。1.1.3.數據架構除SQLServer系統定義的資料架構外，新建架構採用Pascal樣式命名，命名格式為[架構名]。示例：HumanResourcesProduction對資料庫物件Table，View，Procedure，Function等使用資料架構進行歸類。在SQLServer2000中dbo為默認架構。1.1.4.數據表採用Pascal樣式命名，命名格式為[表名]。示例：EmployeeProduct表名以英文單數命名，主要是參考SQLServer2005示例資料庫，個人理解為更好的使用ORM工具生成符合編程規範的代碼（比如C#）。典型的例子，使用Product,而不是Products。1.1.5.資料視圖視圖名稱採用Pascal樣式命名，命名格式為Vew+[視圖名稱]。示例：VewEmployeeVewSalesPerson1.1.6.數據列列名稱命名採用英文單詞或縮寫，英文單詞只來自于具體業務定義，儘量表達清楚含義。採用Pascal樣式命名，命名格式為[列名稱]。示例：AddressIDPostalCode儘量避免使用拼音命名，如果不可避免，對於比較短的列名，採用拼音全寫，如果拼音列名比較複雜，可以採用首個字用全拼，其他字用首字母大寫表示。示例：寧波Ningbo經營方式JingYFS1.1.7.存儲過程建議採用Pascal樣式命名，命名格式為[存儲過程名稱]。示例：USP_GetUser備註：在SQLServer2005示例資料庫中使用Camel樣式命名。如果存在嵌套調用情況﹐避免使用#t1,#t2等臨時表名﹐應用存儲過程名加編號的方式﹐如#SP_GetUser_t11.1.8.函數自定義函數採用Pascal樣式命名，命名格式為[函數名]，系統函數使用全部大寫或者全部小寫。示例：SELECTISNULL(@LastName,'Unknownlastname');GETDATE()1.1.9.用戶定義資料類型採用Pascal樣式命名，命名格式為[自定義資料類型名稱]。示例：FlagNameStyle1.1.10.DML觸發器DML觸發器是當資料庫伺服器中發生資料操作語言(DML)事件時要執行的操作。DML事件包括對表或視圖發出的UPDATE、INSERT或DELETE語句。根據事件不同命名規則使用首碼進行區分,格式為[u|i|d]+[表名|視圖名]示例：Employee_UPDEmployee_INSEmployee_DEL如果存在嵌套調用情況﹐避免使用#t1,#t2等臨時表名﹐應用存儲過程名加編號的方式﹐如#Employee_UPD_t11.1.11.DDL觸發器回應各種資料定義語言(DDL)事件而激發。這些事件主要與以關鍵字CREATE、ALTER和DROP開頭的Transact-SQL語句對應。執行DDL式操作的系統存儲過程也可以激發DDL觸發器。示例：CREATETRIGGER[ddlDatabaseTriggerLog]ONDATABASEFORDDL_DATABASE_LEVEL_EVENTSAS1.1.12.主鍵、外鍵關係和索引除非必可避免﹐否則都應使用外鍵﹐以保証數据的完整性主鍵:PK_[表名稱]_[主鍵]；如果是組合主鍵，使用PK_[表名]_[主鍵1]_[主鍵2]。示例：PK_Store_CustomerIDPK_StoreContact_CustomerID_ContactID外鍵關係：FK_[從表名稱]_[主表名稱]_[外鍵列名稱]。示例：FK_StoreContact_Store_CustomerID聚集索引：PK_[表名稱]_[主鍵]；如果是組合主鍵，使用PK_[表名]_[主鍵1]_[主鍵2]。示例：PK_Store_CustomerIDPK_StoreContact_CustomerID_ContactID唯一非聚集索引:AK_[表名稱]_[列名稱]。示例：AK_Store_rowguid不唯一非聚集索引：PK_[表名稱]_[列名稱]。示例：IX_Store_SalesPersonID主XML索引：PXML_[表名稱]_[Xml類型列名稱]。示例：PXML_Store_Demographics備註：以上命名參考SqlServer2005示例資料庫，一般只需設計器自動生成，不需要額外修改。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編寫規范編寫語句﹐不寫非標准語句﹐如*=﹑leftjoin語句之類﹐重要部分需加入備注3.1.大小寫大寫所有的T-SQL關鍵字，包括T-SQL函數。變數名稱及游標名稱使用Pascal樣式。資料類型定義使用全部大寫或者小寫。示例：DECLARE@LastNameNVARCHAR(32);3.2.使用“;”使用“;”作為Transact-SQL語句終止符。雖然分號不是必需的，但使用它是一種好的習慣。示例：USEAdventureWorks;GODECLARE@findVARCHAR(30);SET@find='Man%';SELECTLastName,FirstName,PhoneFROMPerson.ContactWHERELastNameLIKE@find;3.3.存儲格式儘量採用Unicode資料存儲格式，提高可攜性和相容性，實際應用中使用nchar、nvarchar、ntext代替char、varchar、text。3.4.類型選擇在只有兩個可能數值時，使用bit代替int或smallint。在SQLServer2005中，使用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.語句縮進一個嵌套代碼塊中的語句使用四個空格的縮進。使用MicrosoftSQLServerManagementStudio,選擇“工具”功能表，打開“選項”功能表，在選項對話方塊中選擇文本編輯器-純文本-跳位字元,選中“插入空格單選框”,設置“跳位字元大小”為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首碼，避免引起資料的不一致。示例：--AssumesthedefaultcodepageisnotGreekCREATETABLE#t1(c1nchar(1))INSERT#t1VALUES(N'Ω')INSERT#t1VALUES('Ω')SELECT*FROM#t1輸出結果：c1----ΩO3.17.BEGIN...END塊在SQL代碼快中儘量使用BEGIN...END語句塊，提高代碼可閱讀性。3.18.TRY塊在SQLServer2005中增加TRY塊，Transact-SQL語句組可以包含在TRY塊中。如果TRY塊內部發生錯誤，則會將控制傳遞給CATCH塊中包含的另一個語句組。示例：BEGINTRYSQL語句組1ENDTRYBEGINCATCHSQL語句組2ENDCATCH;3.19.TOP子句在SQLServer2005中加強了TOP的使用，儘量使用TOP(變數)來減少SQL拼串的使用。3.20.TRANSACTION編寫只要在常式中使用多個資料庫修改語句，包括在一個迴圈中多次執行一個語句，就應考慮聲明顯式事務。在SQLSERVER2005中，增加了TRY塊可進行很好的使用。實例：BEGINTRYBEGINTRANSACTION;UPDATE[HumanResources].[Employee]SET[Title]=@Title,[HireDate]=@HireDate,[CurrentFlag]=@CurrentFlagWHERE[EmployeeID]=@EmployeeID;INSERTINTO[HumanResources].[EmployeePayHistory]([EmployeeID],[RateChangeDate],[Rate],[PayFrequency])VALUES(@Em