SQL for .NET developers Intermediate

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.

Kinds of functions and when to use them
  1. Built-in functionsGETDATE, LEN, ROUND, COALESCE: ready to use in any query
  2. Scalar functionReturns one value, such as a price with tax; can be slow per row on old versions
  3. Inline table-valuedOne SELECT with parameters; expanded into the query, so it uses indexes
  4. Multi-statement table-valuedFills a table variable step by step; flexible but slower on big data
  5. 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

SQL
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.

SQL
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.

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.