Deterministic and Nondeterministic Functions in SQL Server
1. Deterministic functions :
Deterministic functions always result in the same output every time they are called with a fixed set of input values and given the same condition of the database. For example, AVG() function always results the same result given the qualifications stated above.
2. Nondeterministic functions :
Nondeterministic functions result in different output each time they are called with a fixed set of input values even if the database state that they access remains the same. For example, GETDATE() function, results the current date and time value, always a different value.
Built-in Function Determinism :
You cannot impact the determinism of any built-in function. The built-in function might be deterministic or nondeterministic based on the property of the function, implemented by SQL Server. For example, using an ORDER BY clause in any query do not change the determinism of the function that is used in the query.
Deterministic built-in functions are :
ABS |
DATEDIFF |
POWER |
ACOS |
DAY |
RADIANS |
ASIN |
DEGREES |
ROUND |
ATAN |
EXP |
SIGN |
ATN2 |
FLOOR |
FLOOR |
SIN |
CEILING |
ISNULL |
SQUARE |
COALESCE |
ISNUMERIC |
SQRT |
DATEADD |
NULLIF |
COS |
LOG10 |
LOG |
YEAR |
TAN |
DATALENGTH |
COT |
MONTH |
The below functions are not always deterministic but could be deterministic when they are specified in a deterministic manner:
- CAST is deterministic until used with DateTime, smalldatetime, or sql_variant.
- ISDATE is deterministic only in case used with CONVERT function.
- CONVERT is deterministic until one of these conditions exists.
- Source type may be sql_variant.
- Target type may sql_variant & its source type is nondeterministic.
Nondeterministic built-in functions are :
@@CONNECTIONS |
LAG |
@@DBTS |
LAST_VALUE |
@@IDLE |
LEAD |
@@CPU_BUSY |
MIN_ACTIVE_ROWVERSION |
@@IO_BUSY |
NEWID |
@@PACK_RECEIVED |
NEWSEQUENTIALID |
@@MAX_CONNECTIONS |
NEXT VALUE FOR |
@@PACK_SENT |
NTILE |
@@PACKET_ERRORS |
PARSENAME |
@@TIMETICKS |
PERCENTILE_CONT |
@@TOTAL_ERRORS |
PERCENTILE_DISC |
@@TOTAL_READ |
CUME_DIST |
@@TOTAL_WRITE |
PERCENT_RANK |
GETUTCDATE |
FORMAT |
GETDATE |
RAND |
GET_TRANSMISSION_STATUS |
DENSE_RANK |
CURRENT_TIMESTAMP |
FIRST_VALUE |
RANK |
ROW_NUMBER |
Last Updated :
22 Sep, 2020
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...