Deterministic and Nondeterministic function in SQL Server

 

The terms deterministic and nondeterministic refer to the predictability of the results returned by functions, specifically when a function is called with the same set of inputs.

Deterministic Functions

A function is deterministic if it always returns the same result when called with the same input values, regardless of when or where it's called. Deterministic functions are predictable and do not depend on external factors like time or session state.

Examples of Deterministic Functions

Ø  Mathematical functions like ABS (), SQRT (), POWER (), etc.

Ø  String functions like UPPER (), LOWER (), LEN (), etc.

Ø  Logical functions like COALESCE()

Nondeterministic Functions

A function is nondeterministic if it may return different results even when called with the same input values. Nondeterministic functions depend on factors like the system time, session-specific data, or database state, which can vary between executions.

Examples of Nondeterministic Functions

Ø  GETDATE () or SYSDATETIME () - returns the current system date and time, which changes with each call.

Ø  NEWID () - generates a new unique identifier (GUID) each time it is called.

Ø  RAND () - generates a random number that can differ between calls.

Ø  System information functions like @@SPID (returns the current session ID)

Difference between Correlated and Non-correlated Sub queries in SQL Server

Correlated Sub queries

A correlated sub query is a sub query that references columns from the outer (main) query. This means that the sub query executes once for each row processed by the outer query. The results of the sub query depend on the row currently being evaluated in the outer query.

See the example

We have creating two table and inserting some records into both table for this demo

create table depttbl ( deptid int primary key, deptname varchar(50) ) insert into depttbl(deptid,deptname) values (1,'IT'),(2,'HR'),(3,'Finance'),(4,'Admin')   create table emptble (empid int identity(1,1) primary key, ename varchar(50), salary decimal(18,10), deptid int) insert into emptble(ename,salary,deptid) values ('Bagesh',50000,1),('Rahul',80000,1), ('Sunil',30000,2),('Anil',20000,2), ('Ramesh',60000,3),('Suresh',8000,3), ('Ganesh',2000,4),('Mahesh',70000,4)

If we want to find employees whose salary is above the average salary of their department, we could use a correlated sub query as below

SELECT ename, Salary,deptid FROM emptble AS e WHERE Salary > (     SELECT AVG(Salary)     FROM emptble AS e2     WHERE e2.deptid = e.deptid );

See the result below

Non-Correlated Sub queries

A non-correlated sub query is independent of the outer query. It does not reference any columns from the outer query, and it can execute on its own without depending on the data from the outer query. A non-correlated sub query executes only once, and its results are used by the outer query.

For example, we want to find all employees who earn more than the overall average salary across all employees.

SELECT ename, Salary,deptid FROM emptble WHERE Salary > (SELECT AVG(Salary) FROM emptble);

See the output.