Scalar and table-valued functions

A user-defined function (UDF) takes parameters and returns either one value or a table. Unlike a procedure, it is used inside a query: in the SELECT list, in WHERE, or in FROM.

Scalar functions

1CREATE OR ALTER FUNCTION dbo.PriceWithVat (@Price decimal(10,2))2RETURNS decimal(10,2)3AS4BEGIN5    RETURN ROUND(@Price * 1.20, 2);6END;7GO8SELECT Name, Price, dbo.PriceWithVat(Price) AS WithVat9FROM dbo.Products10WHERE dbo.PriceWithVat(Price) > 5011ORDER BY Price DESC;

Name                 Price  WithVat
-------------------  -----  -------
Cordless drill       89.00   106.80
Masonry paint 10 L   54.00    64.80
Plywood sheet 18 mm  42.00    50.40

The body is a BEGIN … END block that must end with RETURN. When you call the function you must include the schema. Without it, SQL Server looks for a built-in function of that name:

1SELECT Name, PriceWithVat(Price) AS WithVat FROM dbo.Products;

Msg 195, Level 15, State 10, Line 1
'PriceWithVat' is not a recognized built-in function name.

Inline table-valued functions

An inline table-valued function is a single SELECT with parameters. Think of it as a view you can pass values to:

1CREATE OR ALTER FUNCTION dbo.OrdersFor (@CustomerId int)2RETURNS TABLE3AS4RETURN5    SELECT o.OrderId, o.OrderDate, o.Status,6           SUM(i.Quantity * i.UnitPrice) AS Total7    FROM dbo.Orders AS o8    JOIN dbo.OrderItems AS i ON i.OrderId = o.OrderId9    WHERE o.CustomerId = @CustomerId10    GROUP BY o.OrderId, o.OrderDate, o.Status;11GO12SELECT * FROM dbo.OrdersFor(4) ORDER BY OrderDate;

OrderId  OrderDate   Status    Total
-------  ----------  -------  ------
      4  2024-03-15  Shipped  524.00
      7  2024-05-05  Shipped  199.50

Because it returns a table, it goes in FROM, and it can be joined, filtered and grouped like one. With CROSS APPLY it runs once per row of another table, which you met in the APPLY lesson:

1SELECT c.FirstName, f.OrderId, f.Total2FROM dbo.Customers AS c3CROSS APPLY dbo.OrdersFor(c.CustomerId) AS f4WHERE f.Status = 'Shipped' AND f.Total > 1005ORDER BY f.Total DESC;

FirstName  OrderId   Total
---------  -------  ------
Dev              4  524.00
Dev              7  199.50
Farid            5  120.50
Ben              2  113.00
Asha             3  112.80

SQL Server expands an inline function into the calling query, as it does with a view. The optimizer sees the whole thing and can use indexes, so an inline function costs nothing extra.

CREATE FUNCTION dbo.OrdersFor (@CustomerId int)
RETURNS TABLE
AS RETURN SELECT o.OrderId, o.OrderDate, o.Status, SUM(…) AS Total
          FROM dbo.Orders AS o JOIN dbo.OrderItems AS i … GROUP BY …;

SELECT c.FirstName, f.OrderId, f.Total
FROM dbo.Customers AS c
CROSS APPLY dbo.OrdersFor(c.CustomerId) AS f
WHERE c.City = N'York';

Multi-statement table-valued functions

When the result needs several steps, a multi-statement function declares a table variable, fills it, and returns it:

1CREATE OR ALTER FUNCTION dbo.StockReport (@Below int)2RETURNS @Report TABLE (ProductId int, Name nvarchar(60), Stock int, Advice nvarchar(20))3AS4BEGIN5    INSERT INTO @Report (ProductId, Name, Stock)6    SELECT ProductId, Name, Stock FROM dbo.Products WHERE Stock < @Below;7 8    UPDATE @Report SET Advice = N'Reorder now' WHERE Stock = 0;9    UPDATE @Report SET Advice = N'Reorder soon' WHERE Advice IS NULL;10    RETURN;11END;12GO13SELECT * FROM dbo.StockReport(30) ORDER BY Stock;

ProductId  Name                Stock  Advice
---------  ------------------  -----  ------------
        9  Wall plugs (100)        0  Reorder now
       12  Safety goggles          0  Reorder now
        2  Cordless drill         12  Reorder soon
       10  Masonry paint 10 L     25  Reorder soon

This is easy to read, but the function is a black box to the optimizer: it runs the body, fills the table variable, and only then carries on. Most multi-statement functions can be rewritten as one SELECT (here a CASE would do). Prefer the inline form.

What functions cannot do

A function must not change anything outside itself. It cannot INSERT, UPDATE or DELETE real tables, call a procedure that does, or use TRY … CATCH. Changing its own table variable, as above, is allowed. The rule is checked when you create the function:

1CREATE OR ALTER FUNCTION dbo.TakeStock (@ProductId int)2RETURNS int3AS4BEGIN5    UPDATE dbo.Products SET Stock = Stock - 1 WHERE ProductId = @ProductId;6    RETURN 1;7END;

Msg 443, Level 16, State 15, Procedure TakeStock, Line 5
Invalid use of a side-effecting operator 'UPDATE' within a function.

Use a stored procedure when you need to change data.

Scalar functions and performance

A scalar function used in a query used to run once per row, as a separate call, and it stopped the query using parallel plans. On a million rows that was very slow. SQL Server 2019 added scalar UDF inlining: simple scalar functions are now merged into the query like inline functions. Not every function qualifies. One that reads the current time, for example, cannot be inlined, and sys.sql_modules tells you which ones can:

1CREATE OR ALTER FUNCTION dbo.DaysSince (@d date)2RETURNS int3AS4BEGIN5    RETURN DATEDIFF(day, @d, GETDATE());6END;7GO8SELECT OBJECT_NAME(object_id) AS FunctionName, is_inlineable9FROM sys.sql_modules10WHERE OBJECT_NAME(object_id) IN (N'PriceWithVat', N'DaysSince')11ORDER BY FunctionName;

FunctionName  is_inlineable
------------  -------------
DaysSince     0
PriceWithVat  1

1CREATE OR ALTER FUNCTION dbo.ProductsInCategory (@CategoryName nvarchar(40))2RETURNS TABLE3AS4RETURN5    SELECT p.Name, p.Price6    FROM dbo.Products AS p7    JOIN dbo.Categories AS c ON c.CategoryId = p.CategoryId8    WHERE c.Name = @CategoryName;9GO10SELECT Name, Price11FROM dbo.ProductsInCategory(N'Timber')12ORDER BY Price;

Name                 Price
-------------------  -----
Timber 2x4 2.4 m      6.80
Plywood sheet 18 mm  42.00