SQL User-Defined Functions (UDFs)

A User-Defined Function (UDF) is a database object that encapsulates reusable SQL code to perform calculations, format strings, manipulate dates, or transform table structures. Unlike system-provided functions (such as ABS(), UPPER(), or GETDATE()), UDFs allow you to define custom logic tailored to your specific application domain.

UDFs accept zero or more input parameters and must return a value—either a single scalar value or an entire tabular result set.

Key Characteristics of UDFs

• Invoked Within Queries: Unlike stored procedures, which are called independently using EXEC or CALL, UDFs are invoked directly inside SQL queries (SELECT, WHERE, HAVING, JOIN).
• Side-Effect Free: Functions cannot modify physical database states. They are generally forbidden from executing data manipulation (INSERT, UPDATE, DELETE on base tables) or data definition (CREATE, ALTER, DROP) operations.
• Deterministic vs. Nondeterministic:
• Deterministic Functions: Always return the exact same result given the same input parameters (e.g., SQUARE(4) always returns 16).
• Nondeterministic Functions: May return different results on each invocation even with identical inputs (e.g., functions using GETDATE() or RAND()).

Categories of User-Defined Functions

SQL functions broadly fall into two main structural types:

                  USER-DEFINED FUNCTIONS (UDFs)
                               |
        +----------------------+----------------------+
        |                                             |
  Scalar Functions                           Table-Valued Functions (TVFs)
  (Returns a single value)                   (Returns a virtual table)
                                                      |
                                       +--------------+--------------+
                                       |                             |
                             Inline TVFs                   Multi-Statement TVFs
                             (Single SELECT)               (Procedural logic + Table Var)

1. Scalar User-Defined Functions

A Scalar UDF takes one or more parameters and returns a single data value (e.g., integer, decimal, string, or date).

Example: Calculating Discounted Price (SQL Server)

CREATE FUNCTION dbo.fn_CalculateDiscountedPrice (
    @UnitPrice DECIMAL(10, 2),
    @DiscountRate DECIMAL(4, 2)
)
RETURNS DECIMAL(10, 2)
AS
BEGIN
    DECLARE @FinalPrice DECIMAL(10, 2);
    
    -- Handle NULL or invalid inputs
    IF @UnitPrice IS NULL OR @DiscountRate IS NULL
        SET @FinalPrice = 0.00;
    ELSE
        SET @FinalPrice = @UnitPrice * (1.00 - @DiscountRate);
        
    RETURN @FinalPrice;
END;
GO

Invoking a Scalar UDF:

In SQL Server, scalar UDFs must be called using a two-part name (schema.function_name):

SELECT 
    ProductName, 
    Price, 
    dbo.fn_CalculateDiscountedPrice(Price, 0.15) AS SalePrice
FROM Products
WHERE Price > 50.00;

Example: PostgreSQL Function Syntax (PL/pgSQL)

In PostgreSQL, scalar and table functions use the CREATE FUNCTION statement with standard procedural languages:

CREATE OR REPLACE FUNCTION fn_calculate_discount(
    price DECIMAL,
    discount_rate DECIMAL
)
RETURNS DECIMAL
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN price * (1 - discount_rate);
END;
$$;

-- Querying the function
SELECT fn_calculate_discount(100.00, 0.20);

2. Table-Valued Functions (TVFs)

A Table-Valued Function returns an entire result set (a virtual table) rather than a single scalar value. TVFs can be used in the FROM or JOIN clauses of SQL queries just like physical tables or views.

A. Inline Table-Valued Functions (ITVFs)

Inline TVFs contain a single SELECT statement without a procedural function body (BEGIN...END). They act like parameterized views and offer excellent query optimizer execution speed.

-- SQL Server Syntax
CREATE FUNCTION dbo.ufn_GetOrdersByCustomer (
    @CustomerID INT
)
RETURNS TABLE
AS
RETURN (
    SELECT 
        OrderID, 
        OrderDate, 
        TotalAmount, 
        Status
    FROM Orders
    WHERE CustomerID = @CustomerID
);
GO

Querying an Inline TVF:

SELECT o.OrderID, o.OrderDate, o.TotalAmount
FROM dbo.ufn_GetOrdersByCustomer(101) o
WHERE o.Status = 'Completed';

B. Multi-Statement Table-Valued Functions (MSTVFs)

Multi-statement TVFs declare a table variable schema explicitly, contain a BEGIN...END block, and use control-flow logic (IF, WHILE, loops) to populate and return the table variable.

CREATE FUNCTION dbo.ufn_GetEmployeeHierarchy (
    @ManagerID INT
)
RETURNS @HierarchyTable TABLE (
    EmployeeID INT,
    EmployeeName VARCHAR(100),
    Role VARCHAR(50),
    Level INT
)
AS
BEGIN
    -- Multi-statement logic to populate table variable
    INSERT INTO @HierarchyTable
    SELECT EmployeeID, FirstName + ' ' + LastName, Role, 1
    FROM Employees
    WHERE ManagerID = @ManagerID;

    RETURN;
END;
GO

UDFs vs. Views vs. Stored Procedures

Performance Warning: Scalar UDF Overhead

While scalar UDFs improve code organization, applying a scalar UDF inside a SELECT projection or WHERE clause across millions of rows can cause severe performance degradation:

• Row-by-Row Execution (RBAR): In many legacy database engines, scalar UDFs execute row-by-row for every row in the result set rather than operating as set-based operations.
• Optimization Barrier: Scalar UDFs can prevent the query optimizer from generating parallel execution plans.
• Best Practice: Where possible, replace scalar UDFs operating over large datasets with Inline Table-Valued Functions, CASE expressions, or set-based inline joins.

Dropping a Function

To permanently remove a user-defined function:

DROP FUNCTION IF EXISTS dbo.fn_CalculateDiscountedPrice;