How to Create User-Defined Functions (UDFs) in SQL Server

How do I create and use user-defined functions in SQL Server?

UDFs in SQL Server are reusable routines that accept parameters, perform calculations or data manipulation, and return a scalar value or table.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why use a SQL Server UDF?

UDFs encapsulate business logic, reduce code duplication, and simplify complex queries by returning either a single value or an inline table expression.

Which UDF types exist?

SQL Server supports scalar functions, inline table-valued functions (iTVF), and multi-statement table-valued functions (mTVF). Choose scalar for single values, iTVF for set-based logic, and mTVF when multiple statements are required.

How do I create a scalar UDF?

Use CREATE FUNCTION schema_name.function_name, define input parameters, the return type, and include a single RETURN statement.

Example – calculate customer lifetime value

This scalar UDF sums all total_amount for a given customer in the Orders table:

CREATE FUNCTION dbo.fn_customer_ltv (@customer_id INT) RETURNS DECIMAL(12,2) AS BEGIN DECLARE @ltv DECIMAL(12,2); SELECT @ltv = SUM(total_amount) FROM Orders WHERE customer_id = @customer_id; RETURN ISNULL(@ltv,0); END; GO

How do I create an inline table-valued UDF?

Return a single SELECT as the function body; the columns match the table definition in the query.

Example – latest orders per customer

CREATE FUNCTION dbo.fn_latest_orders (@since DATE) RETURNS TABLE AS RETURN ( SELECT o.* FROM Orders o WHERE o.order_date >= @since ); GO

How can I consume a UDF in queries?

Call scalar UDFs in SELECT, WHERE, or ORDER BY clauses. Join iTVFs like regular tables.

SELECT c.name, dbo.fn_customer_ltv(c.id) AS lifetime_value FROM Customers c ORDER BY lifetime_value DESC; SELECT * FROM dbo.fn_latest_orders('2024-01-01') lo JOIN Customers c ON c.id = lo.customer_id;

What are performance best practices?

Prefer inline TVFs for set logic—they can be inlined by the optimizer. Keep scalar UDFs deterministic and avoid side-effects. Always schema-qualify function names.

How to alter or drop a UDF?

Use ALTER FUNCTION to modify the definition without dropping permissions. Remove a function with DROP FUNCTION schema.function.

Why How to Create User-Defined Functions (UDFs) in SQL Server is important

How to Create User-Defined Functions (UDFs) in SQL Server Example Usage


-- Inline TVF returning top-selling products
CREATE FUNCTION dbo.fn_top_products (@min_qty INT)
RETURNS TABLE
AS
RETURN (
    SELECT p.id, p.name, SUM(oi.quantity) AS total_sold
    FROM OrderItems oi
    JOIN Products p ON p.id = oi.product_id
    GROUP BY p.id, p.name
    HAVING SUM(oi.quantity) >= @min_qty
);
GO

-- Use the TVF in a dashboard query
SELECT tp.name, tp.total_sold, p.stock
FROM dbo.fn_top_products(100) tp
JOIN Products p ON p.id = tp.id
ORDER BY tp.total_sold DESC;

How to Create User-Defined Functions (UDFs) in SQL Server Syntax


CREATE [OR ALTER] FUNCTION [schema_name.]function_name
    ([@param_name data_type [= default] [READONLY], ...])
RETURNS { scalar_data_type | TABLE [RETURN table_type_definition] }
[WITH ( { SCHEMABINDING | ENCRYPTION | EXECUTE AS clause } [, ...] )]
AS
BEGIN
    -- function body: single RETURN for scalar, SELECT/logic for TVF
END;
GO

Common Mistakes

Ignoring the schema when calling a UDF—SQL Server may pick the wrong function or fail. Always use schema-qualified names like dbo.fn_customer_ltv.
Using scalar UDFs inside large result sets—each row invokes the function, causing RBAR (row-by-agonizing-row) performance. Replace with inline TVFs or set-based logic.