T-SQL 編程規範和最佳化技巧

 

1. 變數/函數/預存程序的命名(Variable/UDF/Stored Procedure Naming)

 

2. T-SQL 編碼通訊協定

·        一個嵌套代碼塊中的語句使用四個空格的縮排。（上述代碼中的多行 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)。

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

 

Header Sample Comments

/*****************************************************************

** File: usp_InsertEquipments.sql

** Name: usp_InsertEquipments

** Desc: Insert new equipment number and type in equipment table

**

** Return Values: ErrorCode1: Descr

**                          ErrorCode2: Descr

**

** Call by: Application Module Name

**

** Parameters:

** Input                    Output                             

** ------------          --------------

** @inPara1          @outPara1

** @inPara2

**

** Auth:

** Date:

** ****************************************************************

** Date                   Author                         Description

** ---------           -------------                    ------------------

** 2006-3-31          Jack                            Fixed: 49 Error handing

*******************************************************************/

 

 資料流程式控制制的範例

    /*******************************************************

     ** Process description

   ********************************************************/

Begin

      Insert Order (OrderDate, RequestedById,

                           TargetDate, DestinationLocation)

      Values (@OrderDate, @ContractId,

                    @TargetDate, @LocId)

Set  @ErrorCode=@@error

If @ErrorCode<>0

Begin

        RaiseError (‘Error occurred while inserting Order!’,16,1)

        Return @ErrorCode

End

End

 

• 避免使用 GOTO，錯誤處理程式中除外
• IF …Else的嵌套的級數不要超過4級
• 避免使用遊標,如果使用必須顯示聲明遊標的類型,避免使用動態資料指標.採用批次提交,避免每條都進行提交.                   

 

3. 變數

• 不能像表列那樣定義變數是否為空白,這並不是說變數不可以為空白,在給沒一個變數賦值之前都是空(NULL),所有的輸入參數必須進行有效性檢查.
• 對於不同資料類型的賦值,必須進行顯示的轉換(CONVERT(),CAST())和異常判斷.
• 使用SET對變數賦值,不使用SELECT.
• 通過全域變數來控制錯誤和事務,比如@@ERROR,@@ROWCOUNT

 

4.  T-SQL指令碼最佳化技巧

     

• 對於SELECT/UPDATE語句必須顯示的定義所有的列,避免使用星號.
• 在執行SELECT/INSERT/UPDATE/DELETE語句時,請考慮執行規劃的重用,考慮用SP-EXECUTESQL預存程序
• 優先使用 SELECT...INTO，然後使用 INSERT...SELECT，以避免大量死結
• 如果需要刪除所有的資料,用TRUNCATE TABLE 代替DELETE
• 避免使用DISTINCT 語句
• 如果你需要有限的記錄,通過TOP N代替SET ROWCOUNT來控制排序取值.
• 避免使用SARGABLE的語句在WHERE子句,比如: OR, <>, !=, !<, >!, IS NULL, NOT, NOT IN, NOT LIKE 和LIKE,因為這些操作很難利用已知的索引.
• 避免使用NOT IN,可以採用IN,EXISTS NOT EXISTS和LEFT JOIN 加空值判斷

    --NOT EXISTS, 效率最高

            SELECT a.hdr_key
           FROM hdr_tbl a
            WHERE NOT EXISTS (SELECT * FROM dtl_tbl b WHERE a.hdr_key = b.hdr_key)

   --LEFT JOIN

           SELECT a.hdr_key
           FROM hdr_tbl a
           LEFT JOIN dtl_tbl b ON a.hdr_key = b.hdr_key
           WHERE b.hdr_key IS NULL

   --NOT IN ,效率最低

          SELECT hdr_key
          FROM hdr_tbl
          WHERE hdr_key NOT IN (SELECT hdr_key FROM dtl_tbl)

•      使用EXISTS判斷記錄是否存在.

     --不好的寫法:

        IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx')

     --正確的寫法:

          IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')

 

• 避免在GROUP BY中使用HAVING 語句
• GROUP BY的語句要盡量簡單,不要進行GROUP BY語句的嵌套,避免在GROUP BY中包含多餘的列
• 考慮在GROUP BY的列,進行ORDER BY排序,特別在多使用者的環境下.
• 如果需要在一個包含JOIN的SELECT語句進行GROUP BY,請考慮用子查詢代替JOIN. 如果必須使用GROUP BY, GROUP BY 的應該列在同一張表.
• 如果WHERE條件陳述式有多個AND條件,請確保至少有一個列有索引,如果沒有可以建立多列複合INDEX.
• 對於SQL 無法執行自動最佳化的WHERE條件陳述式,可以通過HINTS顯示的制定INDEX來提高查詢的效率

   --可能不好的寫法:

         SELECT * FROM tblTaskProcesses

WHERE    nextprocess = 1

    AND processid IN (8,32,45)

   --正確的寫法:

     SELECT * FROM tblTaskProcesses (INDEX = IX_ProcessID)

WHERE    nextprocess = 1

AND processid IN (8,32,45)

             

• 儘可能避免在WHERE條件陳述式中使用Function Compute,

   --不好的寫法:

         WHERE SUBSTRING(firstname,1,1) = 'm'

   --正確的寫法:

         WHERE firstname like 'm%'

• 在WHERE條件陳述式中,避免在函數中包列,如果無法避免,請考慮在該列建立INDEX

  --不好的寫法:

          SELECT member_number, first_name, last_name
           FROM members
           WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21

 -- 正確的寫法:

          SELECT member_number, first_name, last_name
           FROM members
           WHERE dateofbirth < DATEADD(yy,-21,GETDATE())

• 在WHERE條件陳述式中,避免使用NOT

   --不好的寫法:

           WHERE NOT column_name > 5

   --正確的寫法:

           WHERE column_name <= 5

• 在WHERE條件陳述式中,推薦使用10位的日前函數.

  --正確的寫法:

         SELECT *

        FROM Northwind.dbo.Orders

        WHERE OrderDate > '12/31/1997'

--不好的寫法:

      SELECT *

        FROM Northwind.dbo.Orders

        WHERE OrderDate > '12/31/97'

 

• 避免使用UNION,而是用UNION ALL
• 使用 SQL-92 標準串連句法,為了提高效能，應優先使用串連，然後使用子查詢或巢狀查詢,表之間的串連使用INNER JOIN,LEFT JOIN 和RIGHT JOIN,不使用CROSS JOIN和多列表方式.
• 多表關聯避免超過5個,可以通過暫存資料表(表變數),簡化複雜的關聯.

 

5. 預存程序的開發和最佳化技巧

• 避免使用觸發器TRIGGER,考慮用預存程序代替觸發器

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

• 考慮用UDF代替預存程序

 --使用表值 UDF 時要小心，因為在變數（而不是常量）中傳遞某個參數時，如果在 WHERE 子句中使用該參數，會導致表掃描。還要避免在一個查詢中多次使用相同的表值 UDF。但是，表值 UDF 確實具有某些非常方便的動態編譯功能

• 對於頻繁調用的預存程序,考慮用SP_RECOMPILE重新編譯
• 使用輸出語句代替返回整個資料集,輸出語句的執行效率會更加高效 
• 在預存程序的頭部使用SET NOCOUNT ON, 通過@@ROWCOUNT來控制,這樣可以減少網路流量和避免潛在的問題, 而在結束時設定 SET NOCOUNT OFF.
• 不使用SP_作為預存程序的名稱,建議用USP_,這個會影響資料庫的執行時間.
• 儘可能使用暫存資料表而不使用暫存資料表,表變數可以減少上鎖和重新編譯的次數並且表變數不使用TEMPDB的空間,而是全部使用記憶體來處理資料.
• 先在常式中建立暫存資料表，最後再顯式刪除暫存資料表。將 DDL 與 DML 語句混合使用有助於處理額外的重新編譯活動
• 儘可能不要在流程式控制制語句中使用暫存資料表,比如:IF .. ELSE, WHILE
• 避免在事務中進行賦值和複雜計算,
•  

--不好的寫法:

          Create procedure proc_1

            As

            Begin

                       Begin transaction

                        -- step 1 verify the data

                        -- step 2 perform calculations

                       -- step 3 get default variable values (date, user info)

                       -- update/insert records

                       commit

            end

  --不好的寫法:

            Create procedure proc_1

            As

            Begin

                       -- step 1 verify the data

                       -- step 2 make calculations

                       -- step 3 get default variable values (date, user info)

                       Begin transaction

                        -- update/insert records

                       commit

            end

 

6. 補充說明

• 上面的一些最佳化規則只是一般原則,在某些特殊情況下可能會有所差別,如果需要分析T-SQL的效能,可以通過查詢分析器的CTRL+L 顯示執行規划進行分析,也可以通過 SET STATISTICS PROFILE ON進行分析.