确定性函数和不确定性函数Deterministic and Nondeterministic Functions

适用对象:yesSQL Server(从 2008 版开始)yesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库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 对上述给定的限定条件始终返回相同的值,但返回当前 datetime 值的 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 数据库引擎SQL Server Database Engine 为函数的结果建立索引的功能。There are several properties of user-defined functions that determine the ability of the SQL Server 数据库引擎SQL 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. 此外,除了样式 20 和 21,小于或等于 100 的样式都具有不确定性。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 仅当指定 seed 参数时,RAND 才是确定性函数。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.