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.