SQL - SQL User-Defined Functions (UDFs)

A User-Defined Function (UDF) in SQL is a custom function created by a database developer to perform a specific operation and return a result. Instead of writing the same calculation or transformation repeatedly in different SQL queries, a UDF allows the logic to be written once and reused whenever required. UDFs are useful for organizing complex calculations, standardizing business rules, and making SQL code easier to maintain.

1. What Is a User-Defined Function?

SQL provides many built-in functions such as SUM(), AVG(), COUNT(), UPPER(), LOWER(), and ROUND(). However, sometimes the built-in functions are not sufficient for a particular application requirement.

A UDF allows the developer to create a custom function for such requirements.

For example, suppose a company frequently needs to calculate an employee's annual salary from their monthly salary. Instead of writing the calculation repeatedly, a function can be created:

CREATE FUNCTION CalculateAnnualSalary
(
    @MonthlySalary DECIMAL(10,2)
)
RETURNS DECIMAL(12,2)
AS
BEGIN
    RETURN @MonthlySalary * 12;
END;

The function can then be used in a query:

SELECT CalculateAnnualSalary(50000) AS AnnualSalary;

The result would be:

AnnualSalary
------------
600000

The exact syntax of UDFs varies between database systems such as SQL Server, PostgreSQL, MySQL, and Oracle.

2. Why Are UDFs Used?

UDFs are mainly used when a particular operation needs to be performed repeatedly.

For example, an organization may have a rule for calculating employee bonuses. Rather than putting the entire calculation into every query, the organization can create a function that accepts the employee's salary and performance score and returns the calculated bonus.

The major advantages include:

  • Reusing the same logic in multiple queries

  • Reducing duplicate SQL code

  • Making complex calculations easier to manage

  • Improving code organization

  • Standardizing frequently used business calculations

  • Making queries easier to read

  • Simplifying maintenance when business rules change

3. Parameters in UDFs

A function can receive one or more parameters. Parameters allow the same function to work with different input values.

For example:

CREATE FUNCTION CalculateDiscount
(
    @Price DECIMAL(10,2),
    @DiscountRate DECIMAL(5,2)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN @Price - (@Price * @DiscountRate / 100);
END;

The function accepts two values:

  • @Price represents the original price.

  • @DiscountRate represents the percentage discount.

It can be called as follows:

SELECT CalculateDiscount(1000, 10) AS FinalPrice;

The result is:

FinalPrice
----------
900

The same function can be used with different prices and discount rates.

4. Return Value

One of the important characteristics of a scalar UDF is that it returns a value.

For example:

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

When the function is called with:

SELECT CalculateTax(5000) AS TaxAmount;

It returns:

TaxAmount
---------
900

The returned value can be used directly in a SELECT statement or, depending on the database system and function type, in other SQL expressions.

5. Scalar and Table-Valued Functions

UDFs can generally be divided into two important categories.

Scalar Functions

A scalar function returns a single value.

For example:

CREATE FUNCTION CalculateSquare
(
    @Number INT
)
RETURNS INT
AS
BEGIN
    RETURN @Number * @Number;
END;

Calling:

SELECT CalculateSquare(8);

returns:

64

Scalar functions are useful for calculations, formatting, conversions, and other operations where one input produces one result.

Table-Valued Functions

A table-valued function returns a table instead of a single value.

For example, a function could accept a department ID and return all employees belonging to that department.

A simplified SQL Server example is:

CREATE FUNCTION GetEmployeesByDepartment
(
    @DepartmentID INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT EmployeeID, EmployeeName, Salary
    FROM Employees
    WHERE DepartmentID = @DepartmentID
);

It can then be queried like a table:

SELECT *
FROM GetEmployeesByDepartment(10);

This makes table-valued functions useful when reusable filtering or data-retrieval logic is required.

6. Using UDFs with Table Data

A UDF can be applied to values stored in a table.

Suppose an Employees table contains:

EmployeeID | EmployeeName | MonthlySalary
-----------|--------------|--------------
101        | Ravi         | 40000
102        | Anu          | 50000
103        | Kiran        | 60000

If a function named CalculateAnnualSalary() has been created, it can be used as follows:

SELECT
    EmployeeName,
    MonthlySalary,
    CalculateAnnualSalary(MonthlySalary) AS AnnualSalary
FROM Employees;

The function is executed for each employee's salary, producing an annual salary value for every row.

7. UDFs and Business Rules

One of the most useful applications of UDFs is implementing reusable business rules.

Consider a company that calculates shipping charges based on order value.

For example:

CREATE FUNCTION CalculateShipping
(
    @OrderValue DECIMAL(10,2)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @Shipping DECIMAL(10,2);

    IF @OrderValue >= 5000
        SET @Shipping = 0;
    ELSE
        SET @Shipping = 100;

    RETURN @Shipping;
END;

The function can be used in an order query:

SELECT
    OrderID,
    OrderValue,
    CalculateShipping(OrderValue) AS ShippingCharge
FROM Orders;

This ensures that the same shipping rule is applied consistently wherever the function is used.

8. Modifying a UDF

When a business requirement changes, the function may need to be modified.

For example, if the company changes the free-shipping threshold from 5,000 to 3,000, the function can be updated according to the syntax supported by the database system.

This is one of the major benefits of centralizing reusable logic. Instead of searching through many queries and changing the same calculation repeatedly, the logic can be maintained in one function.

9. Advantages of UDFs

User-defined functions provide several practical benefits.

Code Reusability:
The same logic can be used in many queries without rewriting it.

Consistency:
When the same business rule is implemented through one function, different parts of an application can use the same calculation.

Maintainability:
Changes to centralized logic can be easier to manage.

Readability:
A complicated calculation can be replaced by a meaningful function name.

For example:

SELECT CalculateAnnualSalary(MonthlySalary)
FROM Employees;

is easier to understand than repeatedly writing a long salary calculation.

Modularity:
Functions divide large database logic into smaller and more manageable components.

10. Limitations of UDFs

Although UDFs are useful, they should not be used for every situation.

Some scalar functions can introduce performance overhead when they are executed repeatedly for a large number of rows, depending on the database engine and function implementation. Poorly designed functions can therefore affect query performance.

UDFs can also make database logic more difficult to trace if too many functions are nested within one another. Developers should therefore keep functions focused, simple, and well documented.

Another important consideration is that UDF syntax and capabilities differ between database management systems. A function written for SQL Server may require significant changes before it can be used in PostgreSQL, MySQL, or Oracle.

11. UDFs Compared with Built-In Functions

Built-in functions are supplied by the database system.

Examples include:

SELECT UPPER('database');
SELECT LOWER('DATABASE');
SELECT ROUND(125.678, 2);

A UDF, on the other hand, is created by the developer according to a specific requirement.

For example:

SELECT CalculateAnnualSalary(45000);

Therefore, built-in functions provide commonly required operations, while UDFs allow developers to create reusable custom operations.

12. Practical Example

Consider an online shopping database containing a Products table:

ProductID | ProductName | Price
----------|-------------|-------
1         | Keyboard    | 1500
2         | Monitor     | 12000
3         | Mouse       | 800

Suppose the business applies an 18% tax to every product. A UDF can be created to calculate the tax:

CREATE FUNCTION CalculateTax
(
    @Price DECIMAL(10,2)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN @Price * 0.18;
END;

The function can then be used:

SELECT
    ProductName,
    Price,
    CalculateTax(Price) AS Tax
FROM Products;

This produces a calculated tax value for each product.

If the tax calculation needs to be changed later, the function can be updated instead of changing every query that performs the calculation.

13. Important Points to Remember

A User-Defined Function is a custom database function created by a developer. It can accept parameters, process those parameters, and return a result. Depending on the database system, UDFs may return a single value or a table.

UDFs are particularly useful when the same calculation, transformation, filtering rule, or business logic needs to be used repeatedly. They promote reusability, consistency, modularity, and maintainability.

However, UDFs should be designed carefully because excessive use, particularly of row-by-row scalar functions on large datasets, can create performance problems. Developers should also remember that the exact syntax and supported function types depend on the SQL database system being used.