Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

ABS DATEDIFF POWER
ACOS DAY RADIANS
ASIN DEGREES ROUND
ATAN EXP SIGN
ATN2 FLOOR SIN
CEILING ISNULL SQUARE
COALESCE ISNUMERIC SQRT
COS LOG TAN
COT LOG10 YEAR
DATALENGTH MONTH  
DATEADD NULLIF

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

@@CONNECTIONS  
GETDATE
@@CPU_BUSY
GETUTCDATE
NEWID
PARSENAME
RAND

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-

1.https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements