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.

Table Valued Function operator in SQL server execution plan

The Table-Valued Function (TVF) operator in an execution plan appears when a query calls a table-valued function. A table-valued function returns a result set (a table), and it can be either inline or multistatement.

Characteristics of the Table-Valued Function Operator

Ø  Row-by-Row Processing: Multistatement TVFs may execute row-by-row processing if they contain complex logic, which can impact performance.

Ø  Execution Plan Representation: The TVF operator shows up in the execution plan when SQL Server calls a function that returns a table.

Ø  Potential Performance Impact: Inline TVFs generally perform better since SQL Server can optimize them along with the main query.

Icon of Table Valued function    

See the example.

For the demo we have create a table and inserting some records.

CREATE TABLE Sales (     SaleID INT PRIMARY KEY,     ProductID INT,     SaleDate DATE,     Quantity INT,     Price DECIMAL(10, 2) );  INSERT INTO Sales (SaleID, ProductID, SaleDate, Quantity, Price) VALUES     (1, 101, '2022-01-01', 10, 100.00),     (2, 102, '2022-01-02', 20, 150.00),     (3, 101, '2022-01-03', 5, 100.00),     (4, 103, '2022-01-04', 8, 200.00),     (5, 102, '2022-01-05', 15, 150.00);

Table created and data inserted successfully. Now we are creating User defined function that return the table value.

CREATE FUNCTION dbo.GetTotalSalesPerProduct (@ProductID INT) RETURNS @SalesSummary TABLE (     ProductID INT,     TotalQuantity INT,     TotalRevenue DECIMAL(10, 2) ) AS BEGIN     INSERT INTO @SalesSummary     SELECT         ProductID,         SUM(Quantity) AS TotalQuantity,         SUM(Quantity * Price) AS TotalRevenue     FROM Sales     WHERE ProductID = @ProductID     GROUP BY ProductID;       RETURN; END;

Function created successfully.

Running the below query

SELECT ProductID, TotalQuantity, TotalRevenue FROM dbo.GetTotalSalesPerProduct(102);

See the result. 

Now see the execution plan.

 

Inserted Scan operator in SQL server execution plan

The Inserted Scan operator appears in an execution plan when working with triggers, specifically AFTER INSERT or INSTEAD OF INSERT triggers. This operator is used to scan the inserted pseudo-table, which is a temporary structure that SQL Server creates to hold the rows that are being inserted into a table within the context of a trigger. The Inserted Scan operator accesses these rows for any additional processing defined in the trigger.

Icon of Inserted scan

See the example

For the demo we are creating employee table and audit table and creating trigger on the employee table.

-- Create Employees table CREATE TABLE Employees (     EmployeeID INT PRIMARY KEY IDENTITY,     FirstName NVARCHAR(50),     LastName NVARCHAR(50),     Position NVARCHAR(50),     HireDate DATE );  -- Create EmployeeAudit table to store logs CREATE TABLE EmployeeAudit (     AuditID INT PRIMARY KEY IDENTITY,     EmployeeID INT,     FirstName NVARCHAR(50),     LastName NVARCHAR(50),     Position NVARCHAR(50),     HireDate DATE,     AuditDate DATETIME DEFAULT GETDATE() );

Now creating trigger on employee table

-- Create AFTER INSERT trigger on Employees table CREATE TRIGGER trgAfterInsertEmployee ON Employees AFTER INSERT AS BEGIN     -- Insert the new employee data into the audit table     INSERT INTO EmployeeAudit (EmployeeID, FirstName, LastName, Position, HireDate)     SELECT EmployeeID, FirstName, LastName, Position, HireDate     FROM inserted; END;

Now inserting a record into the table.

INSERT INTO Employees (FirstName, LastName, Position, HireDate) VALUES ('Bagesh Kumar', 'Singh', 'Software Developer', GETDATE());

Run this script and see the execution plan.

Read more about trigger: Triggerin SQL server

https://bageshkumarbagi-msbi.blogspot.com/2017/08/trigger-in-sql-server.html