Thursday, September 4, 2014

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! 

No comments: