Saturday, 2 November 2024

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.

 

4 comments:

  1. i love your blog because u posted a interesting topics so i will follow the blog
    Power BI training in Kphb

    ReplyDelete
  2. Trapstar isn't just a fashion brand; trapstar cultural movement. The community surrounding trapstar tracksuit brand is deeply rooted in the values of authenticity, self-expression, and empowerment. trap star a loyal following, particularly among young people who identify with the brand’s message trapstar tracksuit overcoming adversity, staying true to oneself, and embracing the hustle.As streetwear continues to gain mainstream popularity, trapstar issue of sustainability has become an increasingly important topic. Trapstar, like trapstar kurtka other brands in the fashion industry, trapstar polska taken steps toward creating more eco-friendly and sustainable clothing options.Chrome Hearts is synonymous with luxury, edginess, chrome hearts jeans unparalleled craftsmanship. Among their chrome hearts ring coveted items, Chrome Hearts jeans stand out as a statement piece, merging chrome hearts ring streetwear aesthetics with high-end fashion. In this article, we’ll dive deep into what makes Chrome Hearts jeans so special, their design features, and why they have become a staple for fashion enthusiasts.Warren Loats is a name etched in the annals warren lotas sports history, primarily for his role as an athlete in the early 20th century. While his fame warren lotas not rival that of global icons, Loats occupies a unique place in the history of college sports, particularly within the context of American football.Hellstar is a captivating and thrilling narrative hellstar plunges into the depths of space, offering a hellstar hoodie blend of cosmic horror, adventure, and exploration. This story unfolds in an eerie and mysterious hellstar where the boundaries between life hellstar hoodie death blur, and the very fabric of reality is tested.

    ReplyDelete
  3. Architectural drafting services refer to the services of drafting detailed technical plans of designs that are applied in the construction of buildings and these may include plans of the building floor, exterior views, cross-sectional views and site plans. These services are critical for architects, builders and developers to have clear pictures and be able to implement construction plans optimal. The sophisticated programs such as AutoCAD, Revit among others and experienced drafters guarantee that all layouts meet the set regulations and client demands. Architectural drafting services play a vital role in streamlining product development, reducing possible confusions, and in enhancing project’s success when providing well-prepared and comprehensible documentation.
    Architectural drafting services

    ReplyDelete
  4. Hellstar is a bold and edgy hellstar brand that embodies the essence of rebellion and strength. Known for its striking designs and daring aesthetics, Hellstar creates a fusion of dark, cosmic-inspired themes with contemporary fashion. From statement graphic apparel to accessories hellstar hoodie that exude attitude, the brand resonates with those who embrace individuality and fearlessly carve their own path. Hellstar isn’t just a label; it’s a lifestyle for the hellstar shirt fearless and unapologetic

    ReplyDelete

If you have any doubt, please let me know.

Popular Posts