Deterministic and Non-Deterministic Functions in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

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. At the end of this article, you will understand what are Deterministic and Non-Deterministic Functions in SQL Server.

Example: Deterministic and Non-Deterministic Functions in SQL Server

We are going to use the following Employee table to understand Deterministic and Non-Deterministic Functions in SQL Server.

Please use the below script to create and populate the Employee table with sample 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. For examples: SQUARE(), Power(), Sum(), AVG(), and Count(). All aggregate functions are deterministic functions. Let us see some examples.

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. For 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 the next article, I am going to discuss Transaction Management in SQL Server with Examples. Here, 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.