User-Defined Functions

20.1 Scalar Functions

Scalar UDFs historically ran row-by-row and blocked parallelism. SQL Server 2019+ Scalar UDF Inlining fixes many cases (check sys.sql_modules.is_inlineable).

CREATE OR ALTER FUNCTION dbo.fn_FullName (@First NVARCHAR(50), @Last NVARCHAR(50))
RETURNS NVARCHAR(101)
WITH SCHEMABINDING
AS
BEGIN
    RETURN CONCAT(@First, N' ', @Last);
END
GO
SELECT dbo.fn_FullName(FirstName, LastName) FROM dbo.Customers;

20.2 Inline Table-Valued Functions (iTVF): Preferred

An iTVF acts like a parameterized view. It's expanded into the outer query and optimized together with it.

CREATE OR ALTER FUNCTION Sales.fn_OrdersByCustomer (@CustomerID INT)
RETURNS TABLE
AS
RETURN
(
    SELECT OrderID, OrderDate, Status FROM dbo.Orders WHERE CustomerID = @CustomerID
);
GO
SELECT * FROM Sales.fn_OrdersByCustomer(1);
SELECT c.FirstName, f.* FROM dbo.Customers c CROSS APPLY Sales.fn_OrdersByCustomer(c.CustomerID) f;

20.3 Multi-Statement Table-Valued Functions (MSTVF)

MSTVFs use fixed cardinality estimates. Interleaved execution (2017+) improves this. Prefer an iTVF when possible.

CREATE OR ALTER FUNCTION dbo.fn_TopProducts (@N INT)
RETURNS @Result TABLE (ProductID INT, ProductName NVARCHAR(150), Total DECIMAL(18,2))
AS
BEGIN
    INSERT @Result
    SELECT TOP (@N) p.ProductID, p.ProductName, SUM(oi.LineTotal)
    FROM dbo.Products p JOIN dbo.OrderItems oi ON oi.ProductID = p.ProductID
    GROUP BY p.ProductID, p.ProductName ORDER BY SUM(oi.LineTotal) DESC;
    RETURN;
END

20.4 Function Limitations

Cannot modify database state (no INSERT/UPDATE/DELETE on permanent tables).

Cannot use TRY/CATCH, dynamic SQL, temp tables, or non-deterministic side effects such as NEWID() (GETDATE() is allowed in newer versions).

Cannot call stored procedures (except extended procedures).

20.5 Functions vs Procedures

Scalar / table Result sets, OUTPUT params, return code Usable in SELECT/WHERE

20.6 CLR Integration (Overview)

Write functions, procedures, types, and aggregates in .NET. Requires clr enabled and clr strict security (signing).