T-SQL 編碼通訊協定(擇自MSDN)

T-SQL 編碼通訊協定發布日期： 4/15/2005 | 更新日期： 4/15/2005

Brian Walker

可能讓人覺得很奇怪，但好像的確沒有什麼“正式的”T-SQL 編碼通訊協定。早在 1999 年末的時候，我驚喜地發現 John Hindmarsh 提出的 SQL Server 7.0 標準，我在 2000 年 2 月的社論中對他的某些建議進行了總結。（2000 年 2 月以及本月的“下載”中都包括了 John 原來的標準。）後來，Ron Talmage 撰寫了一系列專欄文章，提出了他對各種“最佳方法”的建議，當然，SQL Server 小組也已正式發布了 SQL Server 最佳方法分析器 (SQLBPA)。現在，一位具有超過 25 年經驗的資料庫管理員和應用程式開發員 Brian Walker 又提出了他的建議和提示。

進行 T-SQL 編程時常常會忽略編碼通訊協定，但這些標準卻是開發小組順利開展工作的關鍵工具。這裡介紹的編碼通訊協定是我多年的開發成果。它們當然還沒有得到普遍接受，而且不可否認，有些標準帶有主觀色彩。我的目的實際上更多的是為了提高大家的意識，而不是吹捧自己是 T-SQL 樣式方面的仲裁者：最重要的是要建立某些合理的編碼通訊協定並遵循這些標準。您在這篇文章中會發現有關 T-SQL 編程的一系列不同的編碼通訊協定、技巧和提示。它們並未以任何特定的優先順序或重要性順序列出。

讓我們從格式開始。表面上，T-SQL 代碼的格式似乎並不重要，但一致的格式可以使您的同事（不論是同一小組的成員還是更大範圍的 T-SQL Team Dev的成員）更輕鬆地瀏覽和理解您的代碼。T-SQL 陳述式有一個結構，遵循一目瞭然的結構使您可以更輕鬆地尋找和確認語句的不同部分。統一的格式還使您可以更輕鬆地在複雜 T-SQL 陳述式中增刪程式碼片段，使調試工作變得更容易。下面是 SELECT 語句的格式樣本：

       SELECT C.Name, E.NameLast, E.NameFirst, E.Number, ISNULL(I.Description,'NA') AS DescriptionFROM tblCompany AS CJOIN tblEmployee AS EON C.CompanyID = E.CompanyIDLEFT JOIN tblCoverage AS VON E.EmployeeID = V.EmployeeIDLEFT JOIN tblInsurance AS ION V.InsuranceID = I.InsuranceIDWHERE C.Name LIKE @NameAND V.CreateDate > CONVERT(smalldatetime,'01/01/2000')ORDER BY C.Name, E.NameLast, E.NameFirst, E.Number, ISNULL(I.Description,'NA')SELECT @Retain = @@ERROR, @Rows = @@ROWCOUNTIF @Status = 0 SET @Status = @Retain

►一個嵌套代碼塊中的語句使用四個空格的縮排。（上述代碼中的多行 SELECT 語句是一個 SQL 陳述式。）在同一語句中開始新行時，使 SQL 關鍵字靠右對齊。將代碼編輯器配置為使用空格，而不是使用定位字元。這樣，不管使用何種程式查看代碼，格式都是一致的。

►大寫所有的 T-SQL 關鍵字，包括 T-SQL 函數。變數名稱及游標名稱使用混和大小寫。資料類型使用小寫。

►表名別名要簡短，但意義要盡量明確。通常，使用大寫的表名作為別名，使用 AS 關鍵字指定表或欄位的別名。

►當一個 T-SQL 陳述式中涉及到多個表時，始終使用表名別名來限定欄位名。這使其他人閱讀起來更清楚，避免了含義模糊的引用。

►當相關數字出現在連續的程式碼中時（例如一系列 SUBSTRING 函數調用），將它們排成列。這樣容易瀏覽數字列表。

►使用一個（而不是兩個）空行分隔 T-SQL 代碼的邏輯塊，只要需要就可以使用。

►聲明 T-SQL 局部變數（例如 @lngTableID）時，使用適當的資料類型聲明和一致的大寫。

►始終指定字元資料類型的長度，並確保允許使用者可能需要的最大字元數，因為超出最大長度的字元會丟失。

►始終指定十進位資料類型的精度和範圍，否則，將預設為未指定精度和整數範圍。

►使用錯誤處理程式，但要記住行首 (BOL) 中的錯誤檢查樣本不會象介紹的那樣起作用。用來檢查 @@ERROR 系統函數的 T-SQL 陳述式 (IF) 實際上在進程中清除了 @@ERROR 值，無法再捕獲除零之外的任何值。（即使樣本起作用，它們也只能捕獲最後發生的一個錯誤，而不是您更想捕獲的第一個錯誤。）必須使用 SET 或 SELECT 立即捕獲錯誤碼，如前面樣本所示。如果狀態變數仍然為零，應轉換到狀態變數。

►避免使用“未聲明的”功能，例如系統資料表中未聲明的列、T-SQL 陳述式中未聲明的功能或者未聲明的系統預存程序或擴充的預存程序。

►不要依賴任何隱式的資料類型轉換。例如，不能為數字變數賦予字元值，而假定 T-SQL 會進行必要的轉換。相反，在為變數賦值或比較值之前，應使用適當的 CONVERT 函數使資料類型相匹配。另一個樣本：雖然 T-SQL 會在進行比較之前對字元運算式進行隱式且自動的 RTRIM，但不能依賴此行為，因為相容性層級設定非字元運算式會使情況複雜化。

►不要將空的變數值直接與比較子（符號）比較。如果變數可能為空白，應使用 IS NULL 或 IS NOT NULL 進行比較，或者使用 ISNULL 函數。

►不要使用 STR 函數進行舍入，此函數只能用於整數。如果需要十進位值的字串形式，應先使用 CONVERT 函數（轉至不同的範圍）或 ROUND 函數，然後將其轉換為字串。也可以使用 CEILING 和 FLOOR 函數。

►使用數學公式時要小心，因為 T-SQL 可能會將運算式強制理解為一個不需要的資料類型。如果需要十進位結果，應在整數常量後加點和零 (.0)。

►決不要依賴 SELECT 語句會按任何特定順序返回行，除非在 ORDER BY 子句中指定了順序。

►通常，應將 ORDER BY 子句與 SELECT 語句一起使用。可預知的順序（即使不是最方便的）比不可預知的順序強，尤其是在開發或調試過程中。（部署到生產環境中之前，可能需要刪除 ORDER BY 子句。）在返回行的順序無關緊要的情況下，可以忽略 ORDER BY 的開銷。

►不要在 T-SQL 代碼中使用雙引號。應為字元常量使用單引號。如果沒有必要限定對象名稱，可以使用（非 ANSI SQL 標準）括弧將名稱括起來。

►在 SQL Server 2000 中，盡量使用表變數來代替暫存資料表。如果表變數包含大量資料，請注意索引非常有限（只有主鍵索引）。

►先在常式中建立暫存資料表，最後再顯式刪除暫存資料表。將 DDL 與 DML 語句混合使用有助於處理額外的重新編譯活動。

►要認識到暫存資料表並不是不可使用，適當地使用它們可以使某些常式更有效，例如，當需要重複引用大型表或常用表中的某個資料集時。但是，對於一次性事件，最好使用匯出表。

►使用表值 UDF 時要小心，因為在變數（而不是常量）中傳遞某個參數時，如果在 WHERE 子句中使用該參數，會導致表掃描。還要避免在一個查詢中多次使用相同的表值 UDF。但是，表值 UDF 確實具有某些非常方便的動態編譯功能。[相關資料：參閱 Tom Moreau 在 2003 年 11 月份“產生序號”專欄中的“使用 UDF 填充表變數”。－編者按］

►幾乎所有的預存程序都應在開始時設定 SET NOCOUNT ON，而在結束時設定 SET NOCOUNT OFF。[SET NOCOUNT ON 使 SQL Server 無需在執行預存程序的每個語句後向用戶端發送 DONE_IN_PROC 訊息。- 編者按] 此標準同樣適用於觸發器。

►只要在常式中使用多個資料庫修改語句，包括在一個迴圈中多次執行一個語句，就應考慮聲明明確交易。

►使用基於游標的方法或暫存資料表方法之前，應先尋找基於集的解決方案來解決問題。基於集的方法通常更有效。

►與暫存資料表一樣，游標並不是不可使用。對小型資料集使用 FAST_FORWARD 游標通常要優於其他逐行處理方法，尤其是在必須引用幾個表才能獲得所需的資料時。在結果集中包括“合計”的常式通常要比使用游標執行的速度快。如果開發時間允許，基於游標的方法和基於集的方法都可以嘗試一下，看哪一種方法的效果更好。

►使用包含序號（從 1 到 N）的表很方便。

►理解 CROSS JOIN 的工作原理並加以利用。例如，您可以在工作資料表和序號表之間有效地使用 CROSS JOIN，結果集中將包含每個工作資料與序號組合的記錄。

►我的結束語是：T-SQL 代碼往往很簡潔，因此如果某個代碼塊看起來很難處理或重複內容較多，那麼可能存在一種更簡單，更好的方法。

結論

如果您對我的建議有任何看法，歡迎隨時向我寄送電子郵件進行討論，也可以就其他問題提出您的建議。我希望您將此作為談話的開場白。

其他資訊：摘自 Karen 2000 年 2 月份的社論

在標準開發的前沿陣地上，有一股以 SQL Server 資料庫管理員 John Hindmarsh 為首的獨立的新生力量。MCT、MCSE 和 MCDBA 都是最值得您花時間去研究的。John 的貢獻是撰寫了一份詳細的白皮書，概述了他對各種 SQL Server 相關標準提出的建議。我所知道的其他唯一提出類似建議的文章是 Andrew Zanevsky 的《Transact-SQL Programming》(ISBN 1-56592-401-0) 中的“Format and Style”一章。Andrew、SQL Server Professional 的投稿人 Tom Moreau 和 Paul Munkenbeck 以及 John 的朋友兼同事 Stephen James 都為 John 的白皮書做出過貢獻。下面是 John 為編寫預存程序提供的建議樣本：

•	使用 SQL-92 標準串連句法。
•	為了提高效能，應優先使用串連，然後使用子查詢或巢狀查詢。
•	確保變數和參數的類型和大小與表資料列相匹配。
•	確保使用所有變數和參數，或者全部刪除。
•	儘可能將臨時對象放置在本地。
•	只使用在預存程序中建立的暫存資料表。
•	檢查輸入參數的有效性。
•	優先使用 SELECT...INTO，然後使用 INSERT...SELECT，以避免大量死結。
•	維護工作需要的邏輯單元；在可以縮短的情況下，不要建立大量或長時間啟動並執行進程。
•	不要在任何代碼中使用 SELECT *。
•	在過程中使用縮排、塊、定位字元和空格（參閱樣本指令碼）。
•	T-SQL 陳述式要大寫。
•	在過程中添加大量注釋，確保可以識別進程。在有助于澄清處理步驟的地方使用行注釋。
•	包括交易管理，除非要從 MTS 進程中調用過程。（為 MTS 進程編寫獨立的過程。）
•	監視 @@TRANCOUNT 以確定事務的責任層級。
•	避免使用 GOTO，錯誤處理程式中除外。
•	避免使用嵌套過程。
•	避免隱式解析對象名稱，確保所有對象都歸 dbo 所有。

下載 412BRIAN.ZIP

連結至www.microsoft.com/downloads/details.aspx?displayla%20ng=en&familyid=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en

有關 SQL Server Professional 和 Pinnacle Publishing 的詳細資料，請訪問其 Web 網站 http://www.pinpub.com/

注意：這不是 Microsoft Corporation 的 Web 網站。Microsoft 對該 Web 網站上的內容不承擔任何責任。

本文轉載自 2004 年 12 月份的 SQL Server Professional。除非另行說明，否則著作權 2004 Pinnacle Publishing, Inc.。著作權所有，並保留一切權利。SQL Server Professional 是 Pinnacle Publishing 獨立發行的刊物。未經 Pinnacle Publishing, Inc. 事先同意，不得以任何方式使用或複製本文的任何部分（評論文章中的簡短引用除外）。如需與 Pinnacle Publishing, Inc. 聯絡，請撥打 1-800-788-1900。