Why should you care?

Complex procedural shaping sometimes needs it — prefer inline TVFs or procedures when you can.

See it live — copy this example

Run in SQL Server Management Studio (SSMS) or Azure Data Studio.

USE DataVerse;
CREATE OR ALTER FUNCTION dbo.fn_TopOrders(@Take INT)
RETURNS @Result TABLE (OrderId INT, Amount DECIMAL(10,2))
AS
BEGIN
    INSERT INTO @Result (OrderId, Amount)
    SELECT TOP (@Take) OrderId, Amount
    FROM dbo.Orders
    ORDER BY Amount DESC;
    RETURN;
END
GO
SELECT * FROM dbo.fn_TopOrders(3);

What happened?

• The function fills @Result then returns it.
• Useful demo, but the optimizer often treats @Result poorly compared to an inline TOP query or procedure.

Practice next

1. Create and select from fn_TopOrders(3).
2. Compare plan to a plain TOP query.
3. Prefer a stored procedure for this pattern in your database APIs.
4. Return City as well in @Result.
5. Call with @Take = 1.

Remember

Multi-statement TVFs use table variables. More overhead than inline TVFs. Prefer iTVF or procedures for hot paths.

Legacy TVF still in prod

Old DataVerse report uses multi-statement TVF.

Outcome: Team rewrites hot paths to iTVF/procs during tuning.