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

Cursor operator in SQL Server Execution plan

The Cursor Operator appears in an execution plan when a query is executed within a cursor. Cursors are used for row-by-row processing of data, which can be useful for complex row-level operations that aren’t easy to achieve with standard SQL statements. However, cursors generally have performance drawbacks because SQL Server is optimized for set-based operations, not row-by-row processing.

Open cursor

 

Fetch cursor

For the demo we are creating a table and inserting some records.

CREATE TABLE EmployeeSales (     EmployeeID INT,     SaleAmount DECIMAL(10, 2),     SaleDate DATE );  INSERT INTO EmployeeSales (EmployeeID, SaleAmount, SaleDate) VALUES     (1, 100.00, '2022-01-01'),     (2, 200.50, '2022-01-02'),     (1, 150.00, '2022-01-03'),     (3, 300.00, '2022-01-04'),     (2, 250.00, '2022-01-05');

Now writing cursor to fetch the data

-- Declare variables for cursor processing DECLARE @EmployeeID INT,         @SaleAmount DECIMAL(10, 2),         @TotalSales DECIMAL(10, 2) = 0; -- Declare cursor DECLARE sales_cursor CURSOR FOR   SELECT employeeid,          saleamount   FROM   employeesales   ORDER  BY employeeid; -- Open cursor OPEN sales_cursor; -- Fetch each row from the cursor FETCH next FROM sales_cursor INTO @EmployeeID, @SaleAmount; WHILE @@FETCH_STATUS = 0   BEGIN       -- Calculate running total for each employee       SET @TotalSales = @TotalSales + @SaleAmount;       PRINT 'EmployeeID: '             + Cast(@EmployeeID AS VARCHAR(10))             + ', Total Sales: '             + Cast(@TotalSales AS VARCHAR(20));       -- Fetch the next row       FETCH next FROM sales_cursor INTO @EmployeeID, @SaleAmount;   END; -- Close and deallocate cursor CLOSE sales_cursor; DEALLOCATE sales_cursor;

 Running this script and see the execution plan

Cursor is expensive so we need to avoid cursor.