Deterministic and Non-Deterministic Functions in SQL Server

Deterministic and Non-Deterministic Functions in SQL Server

In this article, I am going to discuss Deterministic and Non-Deterministic Functions in SQL Server with Examples. Please read our previous article where we discussed Encryption and Schema Binding Option in SQL Server.

Please use the 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 becomes 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

In this article, I try to explain the Deterministic and Non-Deterministic Functions in SQL Server with 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.

1 thought on “Deterministic and Non-Deterministic Functions in SQL Server”

Leave a Reply

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