Deterministic and Non-deterministic Functions in SQL Server

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.

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.

If you liked the article hit the Facebook like button.

Keep Learning!