In this article, I am giving a quick overview about Deterministic and Non-deterministic Functions in SQL Server. After completing this article you will understand:
What is Deterministic and Non-deterministic Functions?
What are different Deterministic and Non-deterministic Functions in SQL Server?
Please give your valuable suggestions and feedback to improve this article.
What is Deterministic and Non-deterministic Functions?
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.
What is Deterministic and Non-deterministic Functions?
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.
For example, the function AVG always returns the same result given the qualifications stated above
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.
For example, the GETDATE function, which returns the current datetime value, always returns a different result at every run.
What are different Deterministic and Non-deterministic Functions in SQL Server?
Deterministic Functions in SQL SERVER
All built-in string functions are deterministic.
ASCII
|
CHAR
|
CHARINDEX
|
CONCAT
|
DIFFERENCE
|
FORMAT
|
LEFT
|
LEN
|
LOWER
|
LTRIM
|
NCHAR
|
PATINDEX
|
QUOTENAME
|
REPLACE
|
REPLICATE
|
REVERSE
|
RIGHT
|
RTRIM
|
SOUNDEX
|
SPACE
|
STR
|
STUFF
|
SUBSTRING
|
UNICODE
|
UPPER
|
The following built-in functions always deterministic.
ABS
|
ACOS
|
ASIN
|
ATAN
|
ATN2
|
CEILING
|
COALESCE
|
COS
|
COT
|
DATALENGTH
|
ADDDATE
|
DATEDIFF
|
DAY
|
DEGREE
|
EXP
|
FLOOR
|
ISNULL
|
ISNUMERIC
|
LOG
|
LOG10
|
MONTH
|
NULLIF
|
POWER
|
RADIANS
|
ROUND
|
SIGN
|
SIN
|
SQUARE
|
SQRT
|
TAN
|
YEAR
|
The following functions are not always deterministic.
ALL AGGREGRATE FUNCTION
|
CAST
|
CONVERT
|
CHECKSUM
|
ISDATE
|
RAND
|
Non-Deterministic Functions in SQL SERVER
The following built-in functions are always nondeterministic.
@@CONNECTIONS
|
@@TIMETICKS
|
NEWID
|
GETUTCDATE
|
@@CPU_BUSY
|
@@TOTAL_ERRORS
|
NEWSEQUENTIALID
|
GET_TRANSMISSION_STATUS
|
@@DBTS
|
@@TOTAL_READ
|
NEXT VALUE FOR
|
LAG
|
@@IDLE
|
@@TOTAL_WRITE
|
NTILE
|
LAST_VALUE
|
@@IO_BUSY
|
CUME_DIST
|
PARSENAME
|
LEAD
|
@@MAX_CONNECTIONS
|
CURRENT_TIMESTAMP
|
PERCENTILE_CONT
|
MIN_ACTIVE_ROWVERSION
|
@@PACK_RECEIVED
|
DENSE_RANK
|
PERCENTILE_DISC
|
RAND
|
@@PACK_SENT
|
FIRST_VALUE
|
PERCENT_RANK
|
RANK
|
@@PACKET_ERRORS
|
GETDATE
|
ROW_NUMBER
|
TEXTPTR
|
All the configuration, cursor, metadata, security, and system statistical functions are non deterministic.
Content taken from BOL.
