What is the difference between a scalar function and a stored procedure?
Quick Answer
A function returns a value (scalar or a table), can be used inside a query (SELECT dbo.Fn(x)), and cannot change data or have side effects. A stored procedure can modify data, return multiple result sets, use output parameters and transactions, but can't be called from inside a SELECT. Scalar UDFs in a WHERE or SELECT over many rows are a classic performance trap.
A function returns a value and you can use it inside a query, but it can't modify data. A stored procedure can do anything — insert, update, run transactions, return several result sets, use output parameters — but you can't call it from a SELECT. The big warning is scalar functions: if you call one in the SELECT list or WHERE clause of a query over a million rows, it runs a million times and serialises the plan. The good version is an inline table-valued function, because the optimizer just expands it into the query like a view.
- Scalar UDF called per row → hidden RBAR (row-by-agonising-row); SQL Server 2019 can inline some, but not all.
- Inline table-valued functions are the exception — the optimizer expands them like a view, so they're fast and composable.
- Multi-statement TVFs have poor cardinality estimates — avoid on hot paths.
- Procedures for “do something”; inline TVFs for “reusable parameterised query”.