Deterministic and Non-Deterministic Functions in SQL Server

Deterministic and Non-Deterministic Functions in SQL Server

In this article, I will discuss Deterministic and Non-Deterministic Functions in SQL Server with some examples. Please read below articles before proceeding to this article.

Scalar Function in SQL Server

Inline Table Valued Function

Multi-Statement table Valued Function

Encryption and Schema Binding Option in SQL Server

Please use below script to create and populate the Employee table with some test data.

-- Create Employee Table
CREATE TABLE Employee
(
  ID INT PRIMARY KEY,
  Name VARCHAR(50),
  Gender VARCHAR(50),
  DOB DATETIME,
  DeptID INT
)
GO

-- Populate the Employee Table with test data
INSERT INTO Employee VALUES(1, 'Pranaya', 'Male','1996-02-29 10:53:27.060', 1)
INSERT INTO Employee VALUES(2, 'Priyanka', 'Female','1995-05-25 10:53:27.060', 2)
INSERT INTO Employee VALUES(3, 'Anurag', 'Male','1995-04-19 10:53:27.060', 2)
INSERT INTO Employee VALUES(4, 'Preety', 'Female','1996-03-17 10:53:27.060', 3)
INSERT INTO Employee VALUES(5, 'Sambit', 'Male','1997-01-15 10:53:27.060', 1)
INSERT INTO Employee VALUES(6, 'Hina', 'Female','1995-07-12 10:53:27.060', 2)
GO
Deterministic Functions in SQL server

The Deterministic functions in SQL Server always return the same result every time they are called with a specific set of input values and give the same state of the database.

Examples: SQUARE(), Power(), Sum(), AVG(), and Count()

Note: All aggregate functions are deterministic functions

Example1:

SELECT COUNT(*) FROM Employee

Every time we execute the above statement it will give 6

Example2:

SELECT SQUARE(3)

Every time we execute the above statement it will give 9

Non-Deterministic Functions in SQL Server

The Non-deterministic functions in SQL Server may return different results each time they are called with a specific set of values even if the database state that they access remains the same.

Examples: GetDate() and Current_Timestamp

Example1:

SELECT GetDate()

Every time we execute the above SQL Query, it will give a different result

Example2:

SELECT Current_timestamp

Every time the results will vary, it will return the date and time of the SQL Server

The RAND() function is a Non-Deterministic function but if we provide the seed value the function become deterministic as the same value gets returned for the same seed value.

Example1:

SELECT Rand(1)

Deterministic as it returns the same value every time

Example2:

SELECT Rand()

Non-deterministic as the returned values change every time

SUMMARY

In this article, I try to explain the Deterministic and Non-Deterministic Functions in SQL Server step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *