Why should you care?

Parameterized reusable queries that still optimize well — better than many scalar UDFs.

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_OrdersForCity(@City NVARCHAR(50))
RETURNS TABLE
AS
RETURN
(
    SELECT OrderId, CustomerId, Amount, OrderDate
    FROM dbo.Orders
    WHERE City = @City
);
GO
SELECT f.OrderId, f.Amount, c.FullName
FROM dbo.fn_OrdersForCity(N'Mumbai') AS f
LEFT JOIN dbo.Customers AS c ON c.CustomerId = f.CustomerId;

What happened?

• The function is a parameterized SELECT.
• Callers join it like a table.
• No BEGIN/END multi-statement body — that keeps it inline.

Practice next

1. Create fn_OrdersForCity and query Mumbai.
2. Join to Customers as shown.
3. Check the plan — should look like a normal join + filter.
4. Add @MinAmount parameter to the function.
5. CROSS APPLY the function from Customers.

Remember

iTVF = parameterized view-like SELECT. Optimizer-friendly reuse. Single RETURN SELECT only.

City ops reusable set

Several DataVerse reports call fn_OrdersForCity.

Outcome: One definition, consistent city filters.