決定性與非決定性函數Deterministic and Nondeterministic Functions

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

假設資料庫狀態相同,任何時候以特定的輸入值集來呼叫決定性函數時,一律會傳回相同的結果。Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database. 即使所存取的資料庫維持在相同的狀態,每次以特定的輸入值集來呼叫非決定性函數時,都會傳回不同的結果。Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same. 例如,在上述限定情況下,AVG 函數一律傳回相同的結果,而傳回目前日期時間值的 GETDATE 函數則一律傳回不同的結果。For example, the function AVG always returns the same result given the qualifications stated above, but the GETDATE function, which returns the current datetime value, always returns a different result.

無論是透過呼叫函數之計算資料行上的索引,還是透過參考函數的索引檢視,使用者自訂函數都有幾項屬性可決定 SQL Server Database EngineSQL Server Database Engine 為函數的結果編製索引的能力。There are several properties of user-defined functions that determine the ability of the SQL Server Database EngineSQL Server Database Engine to index the results of the function, either through indexes on computed columns that call the function, or through indexed views that reference the function. 函數的決定性是這類屬性的一種。The determinism of a function is one such property. 例如,如果檢視參考任何不具決定性的函數,則無法在檢視上建立叢集索引。For example, a clustered index cannot be created on a view if the view references any nondeterministic functions. 如需函數屬性 (包括決定性) 的詳細資訊,請參閱 使用者定義函數For more information about the properties of functions, including determinism, see User-Defined Functions.

此主題會識別內建系統函數的決定性,以及當使用者自訂函數的具決定性屬性包含擴充預存程序的呼叫時,所造成的影響。This topic identifies the determinism of built-in system functions and the effect on the deterministic property of user-defined functions when it contains a call to extended stored procedures.

內建函數決定性Built-in Function Determinism

您無法影響任何內建函數的決定論。You cannot influence the determinism of any built-in function. 每個內建函數屬於具決定性或不具決定性,主要取決於 SQL ServerSQL Server實作函數的方式。Each built-in function is deterministic or nondeterministic based on how the function is implemented by SQL ServerSQL Server. 例如,在查詢中指定 ORDER BY 子句不會變更該查詢所使用之函式的決定性。For example, specifying an ORDER BY clause in a query does not change the determinism of a function that is used in that query.

所有的字串內建函數都具有決定性。All of the string built-in functions are deterministic. 如需這些函數的清單,請參閱字串函數 (Transact-SQL)For a list of these functions, see String Functions (Transact-SQL).

在下列內建函數類別目錄中,不屬於字串函數的內建函數一律會視為具有決定性。The following built-in functions from categories of built-in functions other than string functions are always deterministic.

ABSABS DATEDIFFDATEDIFF POWERPOWER
ACOSACOS DAYDAY RADIANSRADIANS
ASINASIN DEGREESDEGREES ROUNDROUND
ATANATAN EXPEXP SIGNSIGN
ATN2ATN2 FLOORFLOOR SINSIN
CEILINGCEILING ISNULLISNULL SQUARESQUARE
COALESCECOALESCE ISNUMERICISNUMERIC SQRTSQRT
COSCOS LOGLOG TANTAN
COTCOT LOG10LOG10 YEARYEAR
DATALENGTHDATALENGTH MONTHMONTH
DATEADDDATEADD NULLIFNULLIF

下列函數並非永遠是具決定性函數,但若是以決定性的方式來指定,則可用於索引檢視表或計算資料行的索引。The following functions are not always deterministic, but can be used in indexed views or indexes on computed columns when they are specified in a deterministic manner.

函數Function 註解Comments
所有彙總函式all aggregate functions 除非為 OVER 與 ORDER BY 子句所指定,否則所有彙總函式都具有決定性。All aggregate functions are deterministic unless they are specified with the OVER and ORDER BY clauses. 如需這些函數的清單,請參閱彙總函數 (Transact-SQL)For a list of these functions, see Aggregate Functions (Transact-SQL).
CASTCAST 除非搭配使用 datetimesmalldatetimesql_variant,否則為決定性函數。Deterministic unless used with datetime, smalldatetime, or sql_variant.
CONVERTCONVERT 除非存在這些條件之一,否則為具決定性函數:Deterministic unless one of these conditions exists:



來源類型為 sql_variantSource type is sql_variant.

目標類型為 sql_variant ,且其來源類型為非決定性函數。Target type is sql_variant and its source type is nondeterministic.

來源或目標類型為 datetimesmalldatetime;其他來源或目標類型為字元字串,而且指定了非決定性的樣式。Source or target type is datetime or smalldatetime, the other source or target type is a character string, and a nondeterministic style is specified. 若要具有決定性,樣式參數必須是常數。To be deterministic, the style parameter must be a constant. 此外,小於或等於 100 的樣式不具決定性,但樣式 20 和 21 除外。Additionally, styles less than or equal to 100 are nondeterministic, except for styles 20 and 21. 大於 100 的樣式具決定性,但樣式 106、107、109 和 113 除外。Styles greater than 100 are deterministic, except for styles 106, 107, 109 and 113.
CHECKSUMCHECKSUM 具決定性,但 CHECKSUM(*) 除外。Deterministic, with the exception of CHECKSUM(*).
ISDATEISDATE 若與 CONVERT 函數一併使用,只有在指定 CONVERT 樣式參數,且樣式不等於 0、100、9 或 109 時,才是具決定性的。Deterministic only if used with the CONVERT function, the CONVERT style parameter is specified and style is not equal to 0, 100, 9, or 109.
RANDRAND RAND 只有在指定 seed 參數時才是具決定性的。RAND is deterministic only when a seed parameter is specified.

所有的組態、資料指標、中繼資料、安全性及系統統計函數都是不具決定性的。All the configuration, cursor, metadata, security, and system statistical functions are nondeterministic. 如需這些函數的清單,請參閱組態函數 (Transact-SQL)游標函數 (Transact-SQL)中繼資料函數 (Transact-SQL)安全性函數 (Transact-SQL)系統統計函數 (Transact-SQL)For a list of these functions, see Configuration Functions (Transact-SQL), Cursor Functions (Transact-SQL), Metadata Functions (Transact-SQL), Security Functions (Transact-SQL), and System Statistical Functions (Transact-SQL).

下列屬於其他類別的內建函數,一律是不具決定性的。The following built-in functions from other categories are always nondeterministic.

@@CONNECTIONS@@CONNECTIONS GETDATEGETDATE
@@CPU_BUSY@@CPU_BUSY GETUTCDATEGETUTCDATE
@@DBTS@@DBTS GET_TRANSMISSION_STATUSGET_TRANSMISSION_STATUS
@@IDLE@@IDLE LAGLAG
@@IO_BUSY@@IO_BUSY LAST_VALUELAST_VALUE
@@MAX_CONNECTIONS@@MAX_CONNECTIONS LEADLEAD
@@PACK_RECEIVED@@PACK_RECEIVED MIN_ACTIVE_ROWVERSIONMIN_ACTIVE_ROWVERSION
@@PACK_SENT@@PACK_SENT NEWIDNEWID
@@PACKET_ERRORS@@PACKET_ERRORS NEWSEQUENTIALIDNEWSEQUENTIALID
@@TIMETICKS@@TIMETICKS NEXT VALUE FORNEXT VALUE FOR
@@TOTAL_ERRORS@@TOTAL_ERRORS NTILENTILE
@@TOTAL_READ@@TOTAL_READ PARSENAMEPARSENAME
@@TOTAL_WRITE@@TOTAL_WRITE PERCENTILE_CONTPERCENTILE_CONT
AT TIME ZONEAT TIME ZONE PERCENTILE_DISCPERCENTILE_DISC
CUME_DISTCUME_DIST PERCENT_RANKPERCENT_RANK
CURRENT_TIMESTAMPCURRENT_TIMESTAMP RANDRAND
DENSE_RANKDENSE_RANK RANKRANK
FIRST_VALUEFIRST_VALUE ROW_NUMBERROW_NUMBER
FORMATFORMAT TEXTPTRTEXTPTR

從函數呼叫擴充預存程序Calling Extended Stored Procedures from Functions

呼叫擴充預存程序的函數是不具決定性的,因為擴充預存程序可能對資料庫造成副作用。Functions that call extended stored procedures are nondeterministic, because the extended stored procedures can cause side effects on the database. 副作用是變更資料庫的全域狀態 (例如更新資料表),或變更檔案或網路這類外部資源 (例如修改檔案或傳送電子郵件訊息)。Side effects are changes to a global state of the database, such as an update to a table, or to an external resource, such as a file or the network; for example, modifying a file or sending an e-mail message. 從使用者定義函式執行擴充預存程序時,請不要仰賴傳回一致的結果集。Do not rely on returning a consistent result set when executing an extended stored procedure from a user-defined function. 不建議您使用會對資料庫造成副作用的使用者自訂函數。User-defined functions that create side effects on the database are not recommended.

從函數內部呼叫時,擴充預存程序無法傳回結果集給用戶端。When called from inside a function, the extended stored procedure cannot return result sets to the client. 將結果集傳回用戶端的任何開放式資料服務 API 都會有 FAIL 傳回碼。Any Open Data Services API that returns result sets to the client, has a return code of FAIL.

擴充預存程序不得連回 SQL ServerSQL ServerThe extended stored procedure can connect back to SQL ServerSQL Server. 而且,程序都無法加入相同的交易成為呼叫擴充預存程序的原始函數。However, the procedure cannot join the same transaction as the original function that invoked the extended stored procedure.

類似從批次或預存程序的引動過程,擴充預存程序是在執行 MicrosoftMicrosoftSQL ServerSQL Server Windows 安全帳戶內容中執行。Similar to invocations from a batch or stored procedure, the extended stored procedure is executed in the context of the MicrosoftMicrosoft Windows security account under which SQL ServerSQL Server is running. 擴充預存程序的擁有者在授與其他使用者執行該程序的權限時,應考慮此資訊安全內容的權限。The owner of the extended stored procedure should consider the permissions of this security context, when granting permissions to other users to execute the procedure.