User-defined functions
Functions package logic you can call inside a query: scalar functions return one value and table-valued functions return rows. This lesson shows both, explains why inline table-valued functions scale best on large tables, and compares functions with stored procedures.
- Built-in functionsGETDATE, LEN, ROUND, COALESCE: ready to use in any query
- Scalar functionReturns one value, such as a price with tax; can be slow per row on old versions
- Inline table-valuedOne SELECT with parameters; expanded into the query, so it uses indexes
- Multi-statement table-valuedFills a table variable step by step; flexible but slower on big data
- Stored procedure (compare)Can change data and use transactions, but cannot be used inside SELECT
For large tables, inline table-valued functions are the safe default.
The idea in plain words
SQL Server has many built-in functions such as GETDATE, LEN and ROUND. A user-defined function is one you write yourself, with parameters and a return value, so a rule like how to calculate a discounted price lives in one place.
Functions are read-only: they cannot insert, update or delete table data. That is what makes them safe to use inside SELECT, WHERE and JOIN.
Scalar function: returns one value
CREATE OR ALTER FUNCTION dbo.fn_PriceWithTax (@price decimal(10,2), @rate decimal(4,2))
RETURNS decimal(10,2)
AS
BEGIN
RETURN ROUND(@price * (1 + @rate / 100), 2);
END;
GO
SELECT Name, Price, dbo.fn_PriceWithTax(Price, 18) AS PriceWithTax
FROM dbo.Products;Inline table-valued function: a view with parameters
An inline table-valued function (TVF) is a single SELECT with parameters. SQL Server expands it into the calling query, just like a view, so the optimizer can use indexes and the function scales to large tables. CROSS APPLY runs it for each row of another table.
CREATE OR ALTER FUNCTION dbo.fn_RecentOrders (@customerId int, @days int)
RETURNS TABLE
AS
RETURN
SELECT OrderId, OrderedAt, Total
FROM dbo.Orders
WHERE CustomerId = @customerId
AND OrderedAt >= DATEADD(day, -@days, SYSUTCDATETIME());
GO
SELECT * FROM dbo.fn_RecentOrders(42, 30) ORDER BY OrderedAt DESC;
-- Last 3 orders of every customer
SELECT c.Name, r.OrderId, r.Total
FROM dbo.Customers c
CROSS APPLY (SELECT TOP (3) * FROM dbo.fn_RecentOrders(c.CustomerId, 365)
ORDER BY OrderedAt DESC) r;Functions on huge tables
The type of function matters a lot once tables reach millions of rows.
- Scalar functions in WHERE or SELECT can run once per row. Before SQL Server 2019 they also blocked parallel plans, so a query over 10 million rows could be many times slower.
- SQL Server 2019 and later can inline many simple scalar functions automatically, which removes most of that cost. Check is_inlineable in sys.sql_modules.
- WHERE dbo.fn_Something(Column) = value cannot use an index on Column. Write the condition on the bare column instead.
- Prefer inline TVFs (one RETURN SELECT). Multi-statement TVFs fill a table variable row by row and the optimizer guesses its size, which leads to slow plans on big data.
Functions or stored procedures?
- Function: returns a value or a table, can be used inside SELECT, WHERE and JOIN, cannot change data.
- Stored procedure: called with EXEC, can insert, update and delete, can use transactions and return several result sets, but cannot be used inside a SELECT.
- Rule of thumb: calculations and reusable filters go in functions; operations that change data go in procedures or your app code.
Common mistakes
- Wrapping indexed columns in functions in WHERE, such as WHERE YEAR(OrderedAt) = 2026. Use a range: OrderedAt >= '2026-01-01' AND OrderedAt < '2027-01-01'.
- Running a scalar function that queries a table for every row of a large result, turning one query into millions.
- Using multi-statement TVFs where one SELECT would do.
- Forgetting the schema name: functions must be called as dbo.fn_Name.
Interview question
What is the difference between a function and a stored procedure in SQL Server?
A function must return a value or a table, cannot change table data, and can be used inside a query (SELECT, WHERE, JOIN, CROSS APPLY). A stored procedure is called with EXEC, can insert, update and delete, can manage transactions, can return several result sets or output parameters, and cannot be used inside a SELECT. Functions suit reusable calculations and filters, and procedures suit operations.
Want the full details? Read the official Microsoft documentation for this topic.