What are user-defined functions (UDFs) in SQL?

User-defined functions (UDFs) in SQL are custom functions created by developers to encapsulate reusable logic within a database. Unlike built-in functions like SUM() or CONCAT(), UDFs allow you to define specific operations tailored to your application’s needs. They accept input parameters, perform calculations or data manipulations, and return a value or a table. UDFs are broadly categorized into two types: scalar functions, which return a single value (e.g., a calculated tax amount), and table-valued functions, which return a result set that can be queried like a regular table (e.g., a filtered list of employees by department). By packaging logic into UDFs, developers can simplify complex queries, reduce code duplication, and improve maintainability.

To create a UDF, you define its structure using SQL’s CREATE FUNCTION syntax. For example, a scalar function to calculate tax might look like this:

CREATE FUNCTION dbo.CalculateTax (@Amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
 RETURN @Amount * 0.15;
END;

This function takes a decimal input and returns the tax value. A table-valued function, on the other hand, might return employees in a specific department:

CREATE FUNCTION dbo.GetEmployeesByDepartment (@DeptID INT)
RETURNS TABLE
AS
RETURN (
 SELECT EmployeeID, Name 
 FROM Employees 
 WHERE DepartmentID = @DeptID
);

Scalar functions are invoked in SELECT or WHERE clauses (e.g., SELECT dbo.CalculateTax(100)), while table-valued functions are used in FROM clauses (e.g., SELECT * FROM dbo.GetEmployeesByDepartment(5)). Some databases also support inline table-valued functions (as shown above) for single-statement results and multi-statement functions with procedural logic inside BEGIN...END blocks.

UDFs offer flexibility but require careful consideration. They promote code reuse—for instance, centralizing business logic like tax calculations ensures consistency across queries. However, scalar UDFs can introduce performance overhead in large datasets because they often execute row-by-row instead of leveraging set-based operations. Table-valued functions are generally more efficient, especially when inline. Additionally, SQL dialects (e.g., T-SQL vs. PostgreSQL’s PL/pgSQL) vary in syntax and capabilities, so implementation details may differ. Developers should weigh readability and maintainability against potential performance trade-offs, using UDFs where they simplify complexity without sacrificing efficiency.