用戶定義函數定義

與編程語言中的函數類似，SQL Server 用戶定義函數是接受參數、執行操作（例如複雜計算）並將操作結果以值的形式返回的常式。 返回值可以是單個標量值或結果集。

用戶定義函數準則

在函數中，將會區別處理導致語句被取消並繼續執行模塊（如觸發器或存儲過程）中的下一個語句的 Transact-SQL 錯誤。 在函數中，上述錯誤會導致停止執行函數。 接下來該操作導致取消調用該函數的語句。

BEGIN...END 塊中的語句不能有任何副作用。 函數副作用是指對具有函數外作用域（例如資料庫表的修改）的資源狀態的任何永久性更改。 函數中的語句唯一能做的更改是對函數上的局部對象（如局部游標或局部變數）的更改。 不能在函數中執行的操作包括：對資料庫表的修改，對不在函數上的局部游標進行操作，發送電子郵件，嘗試修改目錄，以及生成返回至用戶的結果集。

如果 CREATE FUNCTION 語句對在發出 CREATE FUNCTION 語句時不存在的資源產生副作用，SQL Server 將執行該語句。 但在調用函數時， SQL Server 不執行此函數。

在查詢中指定的函數的實際執行次數在優化器生成的執行計劃間可能不同。 示例為 WHERE 子句中的子查詢調用的函數。 子查詢及其函數執行的次數會因優化器選擇的訪問路徑的不同而異。

用戶定義函數分類

標量函數

用戶定義標量函數返回在 RETURNS 子句中定義的類型的單個數據值。 對於內聯標量函數，沒有函數體；標量值是單個語句的結果。 對於多語句標量函數，定義在 BEGIN...END 塊中的函數體包含一系列返回單個值的 Transact-SQL 語句。 返回類型可以是任何數據類型除外text， ntext， image， cursor，和timestamp。

表值函數

用戶定義表值函數返回table數據類型。 對於內聯表值函數，沒有函數主體；表是單個 SELECT 語句的結果集。

系統函數

SQL Server 提供了許多系統函數，可用於執行各種操作。 這些函數不能修改。 有關詳細信息，請參閱內置函數 (Transact-SQL)、系統存儲函數 (Transact-SQL) 和動態管理視圖和函數 (Transact-SQL)。

內置函數

聚合函數

聚合函數對一組值執行計算，並返回單個值。 在 select 列表或 SELECT 語句的 HAVING 子句中允許使用它們。 可以將聚合與 GROUP BY 子句結合使用，來計算行類別的聚合。 

使用 OVER 子句來計算特定範圍內的值的聚合。 OVER 子句不能跟在 GROUPING 或 GROUPING_ID 聚合後。

聚合函數對一組值執行計算，並返回單個值。 除了 COUNT 外，聚合函數都會忽略 Null 值。 

所有聚合函數均為確定性函數。 換言之，每次使用一組特定的輸入值調用聚合函數時，它們所返回的值都是相同的。 有關函數確定性的詳細信息，請參閱確定性函數和不確定性函數。

只能在以下位置將聚合函數作為表達式使用：

• SELECT 語句的選擇列表（子查詢或外部查詢）。
• HAVING 子句。

分析函數

解析函數基於一組行計算聚合值。 不過，與聚合函數不同，分析函數可能針對每個組返回多行。 可以使用分析函數來計算移動平均線、運行總計、百分比或一個組內的前 N 個結果。

排名函數

排名函數為分區中的每一行返回一個排名值。 根據所用函數的不同，某些行可能與其他行接收到相同的值。 排名函數具有不確定性。

行集函數

行集函數 返回可在 SQL 語句中像表引用一樣使用的對象。

標量函數

對單一值進行運算，然後返回單一值。 只要表達式有效，即可使用標量函數。

系統存儲函數

SQL Server 提供了以下組的系統函數：Always On 可用性組函數、變更數據捕獲函數、更改跟蹤函數、據收集器函數、Filestream 和 FileTable 函數、托管備份函數、sys.fn_get_sql、sys.fn_MSxe_read_event_stream、sys.fn_stmt_sql_handle_from_sql_stmt、sys.fn_validate_plan_guide、sys.fn_xe_file_target_read_file、sys.fn_backup_file_snapshots、語義全文搜索函數、系統元數據函數、系統安全函數、系統跟蹤函數。

動態管理視圖函數

動態管理視圖和函數返回可用於監視伺服器實例的運行狀況、診斷故障以及優化性能的伺服器狀態信息。

動態管理視圖和函數分為兩種類型：

• 伺服器範圍內的動態管理視圖和函數。 此類型需要具有該伺服器的 VIEW SERVER STATE 許可權。

• 資料庫範圍內的動態管理視圖和函數。 此類型需要具有該資料庫的 VIEW DATABASE STATE 許可權。

用戶定義函數輸入

用戶定義函數採用零個或多個輸入參數並返回標量值或表。 一個函數最多可以有 1024 個輸入參數。 如果函數的參數有預設值，則調用該函數時必須指定 DEFAULT 關鍵字，才能獲取預設值。 此行為與在用戶定義存儲過程中具有預設值的參數不同，在後一種情況下，忽略參數同樣意味著使用預設值。 

用戶定義函數輸出

用戶定義函數不支持輸出參數。

標量函數返回的是一個數據類型值。

內聯表值函數返回的是一個table。

系統函數用戶執行指定操作，可以返回數據類型值或者table。

用戶自定義函數應用場景

具有重覆代碼、功能和代碼塊的地方，應使用函數以使代碼具有更好的可維護性、可重用性和更少的複雜性。

需要對錶中數據進行簡單處理，例如數學計算時可以考慮使用函數。

只有查詢功能時應優先考慮視圖，包含查詢和其他操作的應優先考慮函數。

用戶自定義函數優點

在 SQL Server 中使用用戶定義函數有以下優點：

• 允許模塊化程式設計。

  只需創建一次函數並將其存儲在資料庫中，以後便可以在程式中調用任意次。 用戶定義函數可以獨立於程式源代碼進行修改。

• 執行速度更快。

  與存儲過程相似，Transact-SQL 用戶定義函數通過緩存計劃併在重覆執行時重用它來降低 Transact-SQL 代碼的編譯開銷。這意味著每次使用用戶定義函數時均無需重新解析和重新優化，從而縮短了執行時間。

  和用於計算任務、字元串操作和業務邏輯的 Transact-SQL 函數相比，CLR 函數具有顯著的性能優勢。 Transact-SQL 函數更適用於數據訪問密集型邏輯。

• 減少網路流量。

  基於某種無法用單一標量的表達式表示的複雜約束來過濾數據的操作，可以表示為函數。 然後，此函數便可以在 WHERE 子句中調用，以減少發送至客戶端的數字或行數。

• 查詢中的 Transact-SQL 用戶定義函數只能針對單個線程執行（串列執行計劃）。

用戶自定義函數缺點

用戶自定義函數不能用於執行一系列改變資料庫狀態的操作。

能在函數中使用的語句有嚴格限制：

• 不支持create、ALTER、drop等DDL(Data Definition Language)命令。
• insert、delete、update只能用在臨時表上。
• 不支持動態SQL。
• 不支持“不確定”的函數，比如常用的getdate。不確定函數是指輸入參數相同，返回結果可能不同的函數。