Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Function Determinism
All functions that exist in SQL Server are either deterministic or nondeterministic. The determinism of a function is defined by the data that is returned by the function. The following outlines the determinism of a function:
-
A function is considered deterministic if it always returns the same result set when it's called with the same set of input values.
-
A function is considered nondeterministic if it doesn't return the same result set when it's called with the same set of input values.
This might sound somewhat complicated, but it really isn't. Take, for example, the DATEDIFF and GETDATE functions. DATEDIFF is deterministic because it will always return the same data every time it's run with the same input parameters. GETDATE is nondeterministic because it will never return the same date every time it's run. Look at the code in Listing 7. This code repeats the DATEDIFF and GETDATE function 10 times, waiting 1 second in between each execution.
Listing 7 Function Determinism
SET NOCOUNT ON
DECLARE @intCounter INT
DECLARE @vchGETDATE VARCHAR(32)
DECLARE @intDATEDIFF INT
SELECT @intCounter = 0
WHILE (@intCounter <= 10)
BEGIN
SELECT @vchGETDATE = CONVERT(VARCHAR(32), GETDATE(), 109)
SELECT @intDATEDIFF = DATEDIFF(dd, '1/1/2000', '1/2/2000')
PRINT '--------------------------'
PRINT @vchGETDATE
PRINT @intDATEDIFF
SELECT @intCounter = @intCounter + 1
WAITFOR DELAY '00:00:01'
END
Previous versions of SQL Server didn't care about the determinism of the functions that were used. SQL Server 2000 has introduced functionality that depends on the determinism of the functions contained in them. Nondeterministic functions can't be used in two specific types of SQL statements:
-
You can't create an index on a computed column if the expression contained in the computed column references a nondeterministic function.
-
You can't create a clustered index on a view if the view references any nondeterministic functions.
User-Defined Function Determinism
When you create a user-defined function, SQL Server records the determinism. The determinism of a user-defined function is determined in how you create the function. A user-defined function is considered deterministic if all the following criteria is met:
-
The function is schema-bound to all database objects that it references.
-
Any functions called by the user-defined function are deterministic. This includes all user-defined and system functions.
-
The function doesn't reference any database objects that are outside its scope. That means the function can't reference any outside tables, variables, or cursors.
When you create a function, SQL Server applies all these criteria to the function to determine its determinism. If a function doesn't pass any one of these checks, the function is marked as nondeterministic. These checks can sometimes produce functions marked as nondeterministic even when you expect them to be marked as deterministic.
Determinism of System Functions
Because you can't modify the functions that are installed with SQL Server, you can't do anything to change the determinism of these functions. You must be aware of the determinism of functions so that you can control the determinism of any functions that you create.
The following system functions are deterministic:
|
All string functions |
ISNULL |
|
All aggregate functions |
ISNUMERIC |
|
ABS |
LOG |
|
ACOS |
LOG10 |
|
ASIN |
MONTH |
|
ATAN |
NULLIF |
|
ATN2 |
PARSENAME |
|
CEILING |
PATINDEX |
|
COALESCE |
POWER |
|
COS |
RADIANS |
|
COT |
ROUND |
|
DATALENGTH |
SIGN |
|
DATEADD |
SIN |
|
DATEDIFF |
SQUARE |
|
DAY |
SQRT |
|
DEGREES |
TAN |
|
EXP |
YEAR |
|
FLOOR |
|
Several functions can be either nondeterministic or deterministic, depending on the data types that they are being used with. You can use these functions in indexes on computed columns and indexed views when they are used in a deterministic way. These functions are as follows:
-
CAST is considered deterministic unless it's used with the datetime, smalldatetime, or sql_variant data types.
-
CONVERT is considered deterministic unless it's used with the datetime, smalldatetime, or sql_variant data types. The function can be considered deterministic when used with the datetime and smalldatetime data types if you also use a style parameter.
-
CHECKSUM is deterministic in all cases except the CHECKSUM(*) operation.
-
ISDATE is deterministic if a style parameter is specified and it's not the 0, 100, 9, or 109 style.
-
RAND is deterministic if you specify a seed value.
Most system functions are always nondeterministic, no matter how they are used. The following functions are always nondeterministic:
|
All configuration functions |
STATS_DATE |
|
All cursor functions |
SYSTEM_USER |
|
All metadata functions |
TEXTPTR |
|
All statistical functions |
TEXTVALID |
|
All security functions |
USER_NAME |
|
@@ERROR |
GETANSINULL |
|
@@IDENTITY |
GETDATE |
|
@@ROWCOUNT |
HOST_ID |
|
@@TRANCOUNT |
HOST_NAME |
|
APP_NAME |
IDENT_INCR |
|
CURRENT_TIMESTAMP |
IDENT_SEED |
|
CURRENT_USER |
IDENTITY |
|
DATENAME |
NEWID |
|
FORMATMESSAGE |
PERMISSIONS |
|
SESSION_USER |
|
These lists will help you figure out the determinism of system functions so that you can establish the determinism of any functions you are creating.