SQL Server › Stored Procedures & Functions

What is the difference between a scalar function and a table valued function in SQL Server?

Beginner

Answer

A scalar function returns a single value, such as a calculated number or a formatted string, and can be used anywhere a single value is expected, while a table valued function returns an entire result set that looks like a table, letting you use it in a FROM clause just like you would use a regular table.

-- Scalar function
CREATE FUNCTION GetFullName (@First VARCHAR(50), @Last VARCHAR(50))
RETURNS VARCHAR(101)
AS BEGIN RETURN @First + ' ' + @Last; END;

-- Table valued function
CREATE FUNCTION GetOrdersByCustomer (@CustomerId INT)
RETURNS TABLE
AS RETURN SELECT * FROM Orders WHERE CustomerId = @CustomerId;

Real-world example An application uses a scalar function to consistently format customer full names across many different reports, while using a table valued function to retrieve a customer's orders directly within a larger query's FROM clause.

Common follow-ups: Why might calling a scalar function on every row of a large table cause performance issues?;What is the difference between an inline and a multi statement table valued function?

Query Optimization & Plans;User Defined Functions