決定性與非決定性函式

適用於:SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體

假設資料庫狀態相同,任何時候以特定的輸入值集來呼叫決定性函式時,一律會傳回相同的結果。 即使所存取的資料庫維持在相同的狀態,每次以特定的輸入值集來呼叫非決定性函式時,都會傳回不同的結果。 例如,在上述限定情況下,AVG 函數一律傳回相同的結果,而傳回目前日期時間值的 GETDATE 函數則一律傳回不同的結果。

無論是透過呼叫函式的計算資料行的索引,還是透過參考函式的索引檢視表,使用者自訂函式的幾項屬性都決定了 SQL Server 資料庫引擎為函式結果編製索引的能力。 函數的決定性是這類屬性的一種。 例如,如果檢視參考了任何非決定性函式,就無法在檢視上建立叢集索引。 如需函數屬性 (包括決定性) 的詳細資訊,請參閱 使用者定義函數

決定性函數必須繫結結構描述。 建立決定性函數時,需使用 SCHEMABINDING 子句。

本文會說明內建系統函式的決定性,以及當使用者自訂函式的決定性屬性包含擴充預存程序的呼叫時,所造成的影響。

判斷函式是否為決定性

您可以查詢函式的 is_deterministic 物件屬性,以確認函式是否為決定性。 下列範例會判斷 Sales.CalculateSalesTax 函式是否為決定性。

SELECT OBJECTPROPERTY(OBJECT_ID('Sales.CalculateSalesTax'), 'IsDeterministic');

內建函式決定性

您無法影響任何內建函式的決定性。 每個內建函式為決定性或非決定性,主要取決於 SQL Server 實作函式的方式。 例如,在查詢中指定 ORDER BY 子句不會變更該查詢所使用函式的決定性。

所有的字串內建函式都具有決定性,除了 FORMAT 以外。 如需這些函式的清單,請參閱字串函式 (Transact-SQL)

在下列內建函數類別目錄中,不屬於字串函數的內建函數一律會視為具有決定性。

  • ABS
  • ACOS
  • ASIN
  • ATAN
  • ATN2
  • CEILING
  • COALESCE
  • COS
  • COT
  • DATALENGTH
  • DATEADD
  • DATEDIFF
  • DAY
  • DEGREES
  • EXP
  • FLOOR
  • ISNULL
  • ISNUMERIC
  • 記錄
  • LOG10
  • NULLIF
  • POWER
  • RADIANS
  • ROUND
  • SIGN
  • SIN
  • SQRT
  • SQUARE
  • TAN

下列函式不會永遠是決定性函式,但若是以決定性的方式來指定,則可用於索引檢視表或計算資料行的索引。

函式 註解
所有彙總函式 除非以 OVER 與 ORDER BY 子句指定,否則所有彙總函式都為決定性。 如需這些函式的清單,請參閱彙總函式 (Transact-SQL)
CAST 除非搭配使用 datetimesmalldatetimesql_variant,否則為決定性函數。
CONVERT 除非存在這些條件之一,否則為具決定性函數:

來源類型為 sql_variant

目標類型為 sql_variant ,且其來源類型為非決定性函數。

來源或目標類型為 datetimesmalldatetime;其他來源或目標類型為字元字串,而且指定了非決定性的樣式。 若要具有決定性,樣式參數必須是常數。 此外,小於或等於 100 的樣式不具決定性,但樣式 20 和 21 除外。 大於 100 的樣式具決定性,但樣式 106、107、109 和 113 除外。
CHECKSUM 決定性,但 CHECKSUM(*) 除外。
ISDATE 若與 CONVERT 函數一併使用,只有在指定 CONVERT 樣式參數,且樣式不等於 0、100、9 或 109 時,才是具決定性的。
RAND RAND 只有在指定 seed 參數時才是具決定性的。

所有的組態、資料指標、中繼資料、安全性及系統統計函數都是不具決定性的。 您可以查看這些函式的清單

下列屬於其他類別的內建函數,一律是不具決定性的。

  • @@CONNECTIONS
  • @@CPU_BUSY
  • @@DBTS
  • @@IDLE
  • @@IO_BUSY
  • @@MAX_CONNECTIONS
  • @@PACKET_ERRORS
  • @@PACK_RECEIVED
  • @@PACK_SENT
  • @@TIMETICKS
  • @@TOTAL_ERRORS
  • @@TOTAL_READ
  • @@TOTAL_WRITE
  • AT TIME ZONE
  • CUME_DIST
  • CURRENT_TIMESTAMP
  • DENSE_RANK
  • FIRST_VALUE
  • FORMAT
  • GETDATE
  • GETUTCDATE
  • GET_TRANSMISSION_STATUS
  • LAG
  • LAST_VALUE
  • LEAD
  • MIN_ACTIVE_ROWVERSION
  • NEWID
  • NEWSEQUENTIALID
  • NEXT VALUE FOR
  • NTILE
  • PARSENAME
  • PERCENTILE_CONT
  • PERCENTILE_DISC
  • PERCENT_RANK
  • RAND
  • RANK
  • ROW_NUMBER
  • TEXTPTR

從函式呼叫擴充預存程序

呼叫擴充預存程序的函數是不具決定性的,因為擴充預存程序可能對資料庫造成副作用。 副作用是變更資料庫的全域狀態 (例如更新資料表),或變更檔案或網路這類外部資源 (例如修改檔案或傳送電子郵件訊息)。 從使用者定義函式執行擴充預存程序時,請不要依賴傳回一致的結果集。 不建議您使用會對資料庫造成副作用的使用者自訂函式。

從函式內部呼叫時,擴充預存程序無法傳回結果集給用戶端。 將結果集傳回用戶端的任何開放式資料服務 API 都會有 FAIL 傳回碼。

擴充預存程序可以連回 SQL Server。 不過,程序無法加入與叫用擴充預存程序的原始函式相同的交易。

類似於來自批次或預存程序的叫用,擴充預存程序是在執行 SQL Server 的 Microsoft Windows 安全帳戶內容中執行。 擴充預存程序的擁有者在授與其他使用者執行該程序的權限時,應考慮此資訊安全內容的權限。

另請參閱