Deterministic and Nondeterministic Functions

This was asked to me in a recent technical interview so i thought of sharing it with you all. Lets first go through the definitions.

Deterministic Functions

Deterministic functions are the functions which will always return the same result any time with same input given with the same state of the database.

Nondeterministic Functions

Nondeterministic functions are the functions that may return different results each time they are called with the same input values even with the same
of the database.

Sample list built in Deterministic Functions

Sample Example of Deterministic Functions | ISNULL()

This function will return the same value each time you execute it on any DB.

--

DECLARE @sample AS INT = NULL
SELECT ISNULL(@sample, 10) Op

Op
-----------
10

(1 row affected)

--

Sample list of built in Nondeterministic Functions

Examples of built in Nondeterministic Functions

This function will return the different value each time you execute it.

--

DECLARE @sample AS INT = NULL
SELECT ISNULL(@sample, 10) Op

Op
-----------
10

(1 row affected)

--

--

DECLARE @sample AS INT = NULL
SELECT ISNULL(@sample, 10) Op

Op
-----------
10

(1 row affected)

--

Refer below for more details-

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com