SQL Server › User Defined Functions

What is the difference between an inline table valued function and a multi statement table valued function?

Beginner

Answer

An inline table valued function consists of a single RETURN statement containing a query, and SQL Server can often optimize it very efficiently, similar to a view, while a multi statement table valued function uses a BEGIN and END block with multiple statements building up a table variable to return, offering more flexibility but generally with a bigger performance cost.

-- Inline table valued function
CREATE FUNCTION dbo.GetActiveCustomers()
RETURNS TABLE
AS RETURN SELECT * FROM Customers WHERE IsActive = 1;

-- Multi statement table valued function
CREATE FUNCTION dbo.GetOrderSummary(@CustomerId INT)
RETURNS @Result TABLE (OrderCount INT, TotalAmount DECIMAL(10,2))
AS BEGIN
  INSERT INTO @Result SELECT COUNT(*), SUM(Amount) FROM Orders WHERE CustomerId = @CustomerId;
  RETURN;
END;

Real-world example A reporting system uses a simple inline table valued function for straightforward filtering, but chooses a multi statement function for a more complex order summary calculation that requires several intermediate steps.

Common follow-ups: Why do inline table valued functions generally perform better than multi statement ones?;When is the added flexibility of a multi statement function actually necessary?

Query Optimization & Plans;Views