|
Transact-SQL編程規(guī)范
1. 概述
1.1. 基本原則
以大小寫敏感編寫SQL語(yǔ)句。
盡量使用Unicode 數(shù)據(jù)類型。
優(yōu)先使用連接代替子查詢或嵌套查詢。
盡量使用參數(shù)化SQL查詢代替語(yǔ)句拼接SQL查詢。
禁止使用[拼音]+[英語(yǔ)]的方式來(lái)命名SQL對(duì)象或變量。
盡量使用存儲(chǔ)過(guò)程代替SQL語(yǔ)句。
1.2. 基本規(guī)范
建議采用Pascal樣式或Camel樣式命名數(shù)據(jù)庫(kù)對(duì)象。
大寫T-SQL語(yǔ)言的所有關(guān)鍵字，謂詞和系統(tǒng)函數(shù)。
2. 命名規(guī)范
在一般情況下，采用Pascal樣式或Camel樣式命名數(shù)據(jù)庫(kù)對(duì)象，使在開(kāi)發(fā)基于數(shù)據(jù)庫(kù)應(yīng)用程序的時(shí)候通過(guò)ORM工具生成的數(shù)據(jù)訪問(wèn)代碼不需要調(diào)整就符合程序開(kāi)發(fā)語(yǔ)言（比如C#）命名規(guī)范。另外，關(guān)系型數(shù)據(jù)庫(kù)同Xml結(jié)合得越來(lái)越緊密,規(guī)范的命名越來(lái)越重要。
在實(shí)際數(shù)據(jù)庫(kù)開(kāi)發(fā)過(guò)程中，如果需求方已經(jīng)提供數(shù)據(jù)庫(kù)設(shè)計(jì)方案，建議以提供的方案為準(zhǔn)；在原有數(shù)據(jù)庫(kù)上進(jìn)行升級(jí)開(kāi)發(fā)時(shí)，在可行的情況下可適當(dāng)做出設(shè)計(jì)調(diào)整以符合編程規(guī)范。
1.3. 對(duì)象命名
1.3.1. 數(shù)據(jù)庫(kù)
第一種方式，采用Pascal樣式命名，命名格式為[項(xiàng)目英文名稱]。
示例：AdventureWorks
第二種方式，采用Pascal樣式命名，命名格式為[項(xiàng)目英文名稱] + Db。
示例：AdventureWorksDb
BizTalkRuleEngineDb
建議采用第一種方式。
1.3.2. 數(shù)據(jù)庫(kù)文件
數(shù)據(jù)文件：[數(shù)據(jù)庫(kù)名稱] + _Data.mdf
日志文件：[數(shù)據(jù)庫(kù)名稱] + _Log.ldf
示例：AdventureWorks_Data.mdf
AdventureWorks_Log.ldf
1.3.3. 關(guān)系型數(shù)據(jù)倉(cāng)庫(kù)
采用Pascal樣式命名，命名格式為[項(xiàng)目英文名稱] + DW。
示例：AdventureWorksDW
1.3.4. 數(shù)據(jù)架構(gòu)
除SQL Server 系統(tǒng)定義的數(shù)據(jù)架構(gòu)外，新建架構(gòu)采用Pascal樣式命名，命名格式為[架構(gòu)名]。
示例：HumanResources
Production
對(duì)數(shù)據(jù)庫(kù)對(duì)象 Table，View，Procedure，Function等使用數(shù)據(jù)架構(gòu)進(jìn)行歸類。在SQL Server 2000中dbo為默認(rèn)架構(gòu)。
1.3.5. 數(shù)據(jù)表
采用Pascal樣式命名，命名格式為[表名]。
示例：Employee
Product
表名以英文單數(shù)命名，主要是參考SQL Server 2005示例數(shù)據(jù)庫(kù)，個(gè)人理解不采用復(fù)數(shù)是為了更好的使用ORM工具生成符合編程規(guī)范的代碼（比如C#）。
示例：使用Product
而不是Products
1.3.6. 數(shù)據(jù)視圖
視圖名稱采用Pascal樣式命名，命名格式為v + [視圖名稱]。
示例：vEmployee
vSalesPerson
1.3.7. 數(shù)據(jù)列
列名稱命名采用英文單詞或縮寫，英文單詞只來(lái)自于具體業(yè)務(wù)定義，盡量表達(dá)清楚含義。采用Pascal樣式命名，命名格式為[列名稱]。
示例：AddressID
PostalCode
盡量避免使用拼音命名，如果不可避免，對(duì)于比較短的列名，采用拼音全寫，如果拼音列名比較復(fù)雜，可以采用首個(gè)字用全拼，其它字用首字母大寫表示。
示例：寧波 Ningbo
經(jīng)營(yíng)方式 JingYFS
1.3.8. 存儲(chǔ)過(guò)程
建議采用Pascal樣式命名，命名格式為[存儲(chǔ)過(guò)程名稱]。
示例：GetUser
AddUser
備注：在SQL Server 2005示例數(shù)據(jù)庫(kù)中使用Camel樣式命名。
1.3.9. 函數(shù)
自定義函數(shù)采用Pascal樣式命名，命名格式為[函數(shù)名]，系統(tǒng)函數(shù)使用全部大寫。
示例：SELECT ISNULL(@LastName,'Unknown last name');
GETDATE()
1.3.10. 用戶定義數(shù)據(jù)類型
采用Pascal樣式命名，命名格式為[自定義數(shù)據(jù)類型名稱]。
示例：Flag
NameStyle
1.3.11. DML觸發(fā)器
DML觸發(fā)器是當(dāng)數(shù)據(jù)庫(kù)服務(wù)器中發(fā)生數(shù)據(jù)操作語(yǔ)言 (DML) 事件時(shí)要執(zhí)行的操作。DML 事件包括對(duì)表或視圖發(fā)出的 UPDATE、INSERT 或 DELETE 語(yǔ)句。根據(jù)事件不同命名規(guī)則使用前綴進(jìn)行區(qū)分,格式為 [u|i|d] + [表名|視圖名]
示例：uEmployee
iEmployee
dEmployee
另外一種方式為，
AFTER 觸發(fā)器：TR_表名_[后面插入加I,修改加U,刪除加D]。
INSTEAD OF 觸發(fā)器：TR_表名或視圖名_OF[后面插入加I,修改加U,刪除加D]
1.3.12. DDL觸發(fā)器
響應(yīng)各種數(shù)據(jù)定義語(yǔ)言 (DDL) 事件而激發(fā)。這些事件主要與以關(guān)鍵字 CREATE、ALTER 和 DROP 開(kāi)頭的 Transact-SQL 語(yǔ)句對(duì)應(yīng)。執(zhí)行 DDL 式操作的系統(tǒng)存儲(chǔ)過(guò)程也可以激發(fā) DDL 觸發(fā)器。
采用Camel樣式命名，命名單詞能夠描述DDL觸發(fā)器功能。
示例：
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT 'You must disable Trigger "safety" to drop or alter tables!'
ROLLBACK ;
另外一種方式為添加ddl前綴，
示例：
CREATE TRIGGER [ddlDatabaseTriggerLog]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
1.3.13. 主鍵、外鍵關(guān)系和索引
主鍵: PK_[表名稱]_[主鍵]；如果是組合主鍵，使用PK_[表名]_[主鍵1]_[主鍵2]。
示例：PK_Store_CustomerID
PK_StoreContact_CustomerID_ContactID
外鍵關(guān)系：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示例數(shù)據(jù)庫(kù)，一般只需設(shè)計(jì)器自動(dòng)生成，不需要額外修改。
1.4. 參數(shù)命名
1.4.1. 數(shù)據(jù)列參數(shù)
命名格式為 @ + [列名稱]。
示例：@EmployeeID
在列名不符合Pascal樣式時(shí)（早期遺留系統(tǒng)），例如使用全部大寫的列名稱，或使用“_”進(jìn)行連接的字段名稱，參數(shù)名稱定義使用 @ + [列名稱]，這里的列名稱盡量符合Pascal樣式命名。
1.4.2. 非數(shù)據(jù)列參數(shù)
在參數(shù)無(wú)法跟列名稱進(jìn)行關(guān)聯(lián)時(shí)，使用能夠反映該參數(shù)功能的英文單詞或單詞組合, 采用Pascal樣式命名。
示例：@ErrorID
@Flag
1.5. 常見(jiàn)命名
1.5.1. 常用字段命名
這里的常用字段是指在建表時(shí)頻繁使用的表名或列名，下表對(duì)常用字段進(jìn)行建議性定義,
列名稱 數(shù)據(jù)類型 說(shuō)明
CreatedDate datetime 紀(jì)錄創(chuàng)建日期，一般使用GETDATE()自動(dòng)生成
ModifiedDate datetime 紀(jì)錄最后修改日期，首次使用GETDATE()
DeletedDate datetime 記錄刪除（標(biāo)記刪除）日期
StartDate datetime 開(kāi)始日期
EndDate datetime 結(jié)束日期
StartTime datetime 開(kāi)始時(shí)間
EndTime datetime 結(jié)束時(shí)間
rowguid uniqueidentifier 唯一標(biāo)識(shí)行的ROWGUIDCOL號(hào)，用于支持合并復(fù)制
ID int 使用ID代替Id或id。一般為自增長(zhǎng)主鍵列
ParentID int 父ID
Status int 狀態(tài)
3. SQL編寫
3.1. 大小寫
大寫T-SQL 語(yǔ)言的所有關(guān)鍵字，謂詞和系統(tǒng)函數(shù)。變量名稱及游標(biāo)名稱使用Pascal樣式。數(shù)據(jù)類型定義使用全部小寫。
示例：DECLARE @LastName nvarchar(32);
3.2. 使用“;”
使用“;”作為 Transact-SQL 語(yǔ)句終止符。雖然分號(hào)不是必需的，但使用它是一種好的習(xí)慣。
示例：
USE AdventureWorks;
GO
DECLARE @find varchar(30);
SET @find = 'Man%';
SELECT LastName, FirstName, Phone
FROM Person.Contact
WHERE LastName LIKE @find;
3.3. 存儲(chǔ)格式
盡量采用Unicode數(shù)據(jù)存儲(chǔ)格式，提高可移植性和兼容性，實(shí)際應(yīng)用中盡量使用nchar、nvarchar、ntext代替char、varchar、text。
3.4. 類型選擇
如果字符具有明確的長(zhǎng)度，使用nchar代替nvarchar；char代替varchar。
在只有兩個(gè)可能數(shù)值時(shí)，使用bit代替int或smallint。
在SQL Server 2005中，使用nvarchar(MAX)代替ntext；varchar(MAX)代替text；varbinary(MAX)代替image。
在特殊的數(shù)據(jù)表結(jié)構(gòu)中可考慮xml數(shù)據(jù)類型，達(dá)到事半工倍的效果。
3.5. 默認(rèn)值
在建立數(shù)據(jù)表時(shí)，盡量使用默認(rèn)值代替NULL值。比如設(shè)置CreatedDate列默認(rèn)值為GETDATE()。在可行的情況下設(shè)置字段為不允許空。
3.6. 字段長(zhǎng)度
始終指定字符數(shù)據(jù)類型的長(zhǎng)度，并確保允許用戶可能需要的最大字符數(shù)，避免超出最大長(zhǎng)度時(shí)出現(xiàn)字符丟失現(xiàn)象。對(duì)于字符型數(shù)據(jù)，建議采用2的n次方來(lái)定義數(shù)據(jù)長(zhǎng)度。
示例：nvarchar(32)
varchar(64)
3.7. 使用“'”
在 T-SQL 代碼中為字符常量使用單引號(hào),避免使用雙引號(hào)。
3.8. 語(yǔ)句縮進(jìn)
一個(gè)嵌套代碼塊中的語(yǔ)句使用四個(gè)空格的縮進(jìn)。使用Microsoft SQL Server Management Studio ,選擇“工具”菜單，打開(kāi)“選項(xiàng)”菜單，在選項(xiàng)對(duì)話框中選擇文本編輯器->純文本->制表符,選中“插入空格單選框”,設(shè)置“制表符大小”為4，縮進(jìn)大小為“4”。
3.9. 語(yǔ)句換行
建議SQL代碼每行以關(guān)鍵字或“'”開(kāi)頭。
示例：
SELECT [ShiftID]
,[Name]
,[StartTime]
,[EndTime]
,[ModifiedDate]
FROM [AdventureWorks].[HumanResources].[Shift]
3.10. 語(yǔ)句分割
使用一個(gè)（而不是兩個(gè)）空行分隔 T-SQL 代碼的邏輯塊。
3.11. 使用“*”
盡量避免在任何代碼中使用 “SELECT *”。
3.12. 表名別名
表名別名要簡(jiǎn)短，但意義要盡量明確。通常使用大寫的表名作為別名，使用 AS 關(guān)鍵字指定表或字段的別名。
3.13. 類型轉(zhuǎn)換
不要依賴任何隱式的數(shù)據(jù)類型轉(zhuǎn)換，不要假定 T-SQL 會(huì)進(jìn)行必要的轉(zhuǎn)換。例如，把數(shù)字變量賦予字符值。相反，在為變量賦值或比較值之前，應(yīng)使用適當(dāng)?shù)?nbsp;CONVERT 函數(shù)使數(shù)據(jù)類型相匹配。
3.14. 數(shù)值比較
不要將空的變量值直接與比較運(yùn)算符（符號(hào)）比較。如果變量可能為空，應(yīng)使用 IS NULL 或 IS NOT NULL 進(jìn)行比較，或者使用 ISNULL 函數(shù)。
3.15. 排序
決不要依賴 SELECT 語(yǔ)句會(huì)按任何特定順序返回行，除非在 ORDER BY 子句中指定了順序。通常，應(yīng)將 ORDER BY 子句與 SELECT 語(yǔ)句一起使用?？深A(yù)知的順序（即使不是最方便的）比不可預(yù)知的順序強(qiáng)，尤其是在開(kāi)發(fā)或調(diào)試過(guò)程中。在返回行的順序無(wú)關(guān)緊要的情況下，可以忽略 ORDER BY ，減少資源開(kāi)銷。
3.16. Unicode字符串
在Unicode字符前面使用N前綴，避免引起數(shù)據(jù)的不一致。
示例：
-- Assumes the default code page is not Greek
CREATE TABLE #t1 (c1 nchar(1))
INSERT #t1 VALUES(N'Ω')
INSERT #t1 VALUES('Ω')
SELECT * FROM #t1
輸出結(jié)果：
c1
----
Ω
O
3.17. BEGIN...END 塊
在SQL代碼快中盡量使用BEGIN...END 語(yǔ)句塊，提高代碼可閱讀性。
3.18. TRY塊
在SQL Server 2005中對(duì)一些可能執(zhí)行失敗的語(yǔ)句盡量使用TRY塊。Transact-SQL 語(yǔ)句組可以包含在 TRY 塊中，如果 TRY 塊內(nèi)部發(fā)生錯(cuò)誤，則會(huì)將控制傳遞給 CATCH 塊中包含的另一個(gè)語(yǔ)句組。
示例：
BEGIN TRY
SQL 語(yǔ)句組1
END TRY
BEGIN CATCH
SQL 語(yǔ)句組2
END CATCH;
3.19. TOP子句
在SQL Server 2005中加強(qiáng)了TOP的使用，盡量使用TOP(變量)來(lái)減少SQL拼串現(xiàn)象。
3.20. TRANSACTION編寫
只要在例程中使用多個(gè)數(shù)據(jù)庫(kù)修改語(yǔ)句，包括在一個(gè)循環(huán)中多次執(zhí)行一個(gè)語(yǔ)句，就應(yīng)考慮聲明顯式事務(wù)。在SQL SERVER 2005 中，增加了TRY塊可進(jìn)行很好的應(yīng)用。
實(shí)例：
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. 存儲(chǔ)過(guò)程
在編寫存儲(chǔ)過(guò)程時(shí)，使用PROCEDURE 代替 PROC 簡(jiǎn)寫。
示例：CREATE PROCEDURE [dbo].[存儲(chǔ)過(guò)程名字]
4. 代碼注釋
4.1. 代碼頭部注釋
在SQL代碼塊（sql文件或存儲(chǔ)過(guò)程）的頭部進(jìn)行注釋，標(biāo)注創(chuàng)建人(Author)、創(chuàng)始日期(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報(bào)關(guān)單回執(zhí)處理
-- Modify [1]: 鄭佐, 2006-12-31, 簡(jiǎn)化邏輯判斷流程
-- Modify [2]: 鄭佐, 2007-01-20, 更新條件判斷
-- ================================================
注：日期格式使用 yyyy-MM-dd。Modify [n] n代表修改序號(hào)，從1開(kāi)始，每次修改加1。
4.2. TRANSACTION注釋
建議在每個(gè)事務(wù)的開(kāi)頭進(jìn)行注釋，說(shuō)明該事務(wù)的功能。
-- < Modifier,Date, Description >
BEGIN TRANSACTION;
5. 附錄A 命名規(guī)則
常見(jiàn)命名規(guī)則有四種樣式：完全大寫、完全小寫、Pascal 大小寫和 Camel 大小寫。
5.1. Pascal 大小寫
組成標(biāo)識(shí)符的每個(gè)單詞的首字母大寫，其余字母小寫的書(shū)寫約定。對(duì)于縮寫的雙字母單詞，要求全部大寫。
例如：ApplicationException
ID
5.2. Camel 大小寫
標(biāo)識(shí)符的首字母小寫，每個(gè)后面連接的單詞的首字母大寫，其余字母小寫的書(shū)寫約定。對(duì)于縮寫的雙字母單詞，要求它們出現(xiàn)在標(biāo)識(shí)符首部時(shí)全部小寫，否則全部大寫。
例如：applicationException
id
5.3. 匈牙利命名法
匈牙利命名法由匈牙利程序員發(fā)明，他在微軟工作了多年，此命名法就是通過(guò)微軟的各種產(chǎn)品和文檔傳出來(lái)。多數(shù)有經(jīng)驗(yàn)的程序員，不管他們用的是哪門語(yǔ)言，都或多或少在使用它。
基本原則：變量名 = 屬性 ＋ 類型 ＋ 對(duì)象描述
即一個(gè)變量名是由三部分信息組成，這樣，程序員很容易理解變量的類型、用途，而且便于記憶。
|
