SQL Server Interview Question #115

What is a multi-statement table-valued function?

Views, Stored Procedures & User-Defined Functions Mid-Level Intermediate

Detailed Explanation

A multi-statement table-valued function returns a table variable that is populated through multiple statements inside the function.

It can express more procedural logic than an inline TVF, but it may provide the optimizer with less useful cardinality information and can perform poorly in some workloads. Use it only when its additional procedural flexibility is genuinely required and validate performance with realistic data.

Code Example

CREATE FUNCTION dbo.GetProductSummary
(
    @CategoryId INT
)
RETURNS @Result TABLE
(
    ProductId INT,
    ProductName NVARCHAR(200),
    Price DECIMAL(18,2)
)
AS
BEGIN
    INSERT INTO @Result (ProductId, ProductName, Price)
    SELECT Id, Name, Price
    FROM dbo.Products
    WHERE CategoryId = @CategoryId;

    RETURN;
END;