Microsoft SQL Deterministic & Non-Deterministic Functions

Microsoft SQL Deterministic & Non-Deterministic Functions

--Deterministic functions tend to be aggregate functions.
--Deterministic functions are functions that output the same value when using the same inputs
--if the state of the database does not change.
--Examples are as follows.

-- Deterministic functions
select sum(1+2+6+4);
select count(*) from dbo.[options ftse];

--Non-Deterministic Functions
--Non-Deterministic functions change even if the state of the database stays the same.
--Non-Deterministic functions tend to be date functions.
--Examples follow.

--Non-Deterministic Functions

select CURRENT_TIMESTAMP;
select getdate();

--Random function called RAND is Deterministic if there is an input value.
--Random function called RAND is Non-Deterministic if there is no input parameter.

-- Deterministic Function

select RAND(5);

-- Non-Deterministic Function

select RAND();