Deterministic and Nondeterministic Functions in SQL Server

Deterministic functions in SQL Server always return the same result for a given set of input values. This means that if you call the function with the same input values multiple times, you will always get the same output. Examples of deterministic functions include mathematical functions like ABS(), ROUND(), and SQRT(), as well as string functions like LEFT(), RIGHT(), and SUBSTRING().

Nondeterministic functions, on the other hand, may return different results for the same input values, depending on the context in which they are called. Examples of nondeterministic functions include functions that return system information like GETDATE(), CURRENT_TIMESTAMP(), and NEWID(). These functions may return different results each time they are called, even if the input values are the same.

It is important to note that the determinism of a function can affect the performance of queries that use that function. If a function is deterministic, SQL Server can cache the results of the function and reuse them in subsequent queries, which can improve performance. However, if a function is nondeterministic, SQL Server cannot cache the results, which can lead to slower query performance.

Here is an example of a deterministic function:

SELECT ABS(-10) -- returns 10
SELECT ABS(10) -- returns 10

Both calls to the ABS() function return the same result, regardless of the context in which they are called.

Here is an example of a nondeterministic function:

SELECT GETDATE() -- returns the current date and time
WAITFOR DELAY '00:00:01'
SELECT GETDATE() -- returns a different date and time than the previous call

The first call to GETDATE() returns the current date and time, but the second call returns a different date and time because one second has elapsed between the two calls.