SQL Functions – Complete Guide

In SQL, a function is a predefined or user-defined routine that performs a specific operation and returns a value. Functions help simplify queries, improve reusability, and make data manipulation more efficient.

Types of SQL Functions

SQL functions are mainly divided into two categories:

1. Built-in Functions

These are predefined functions provided by SQL.

a) Aggregate Functions

Used to perform calculations on a set of values.

• COUNT() – Counts number of rows
• SUM() – Calculates total
• AVG() – Finds average
• MAX() – Finds maximum value
• MIN() – Finds minimum value

Example:

SELECT COUNT(*) AS TotalEmployees FROM Employees;

b) Scalar Functions

Operate on a single value and return a single value.

Common Types:

• String Functions: UPPER(), LOWER(), LEN()
• Numeric Functions: ROUND(), ABS()
• Date Functions: GETDATE(), DATEDIFF()

Example:

SELECT UPPER(Name) FROM Employees;

2. User-Defined Functions (UDFs)

These are custom functions created by users to perform specific tasks.

Types of UDFs:

a) Scalar Functions

Return a single value.

CREATE FUNCTION GetFullName (@FirstName VARCHAR(50), @LastName VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
    RETURN @FirstName + ' ' + @LastName
END

b) Table-Valued Functions

Return a table instead of a single value.

CREATE FUNCTION GetEmployeesByDept (@DeptId INT)
RETURNS TABLE
AS
RETURN (
    SELECT * FROM Employees WHERE DepartmentId = @DeptId
)

Advantages of SQL Functions

• Reusability of logic
• Cleaner and modular code
• Improves readability
• Reduces duplication
• Helps in complex calculations

Limitations of SQL Functions

• Can affect performance if overused
• Limited error handling compared to stored procedures
• Cannot modify database state (in most cases)
• Restrictions on using dynamic SQL

Function vs Stored Procedure

Best Practices

• Use functions for reusable logic
• Avoid heavy computations inside functions
• Prefer inline table-valued functions for performance
• Keep functions simple and optimized

Conclusion

SQL functions are powerful tools that help simplify data operations and improve code maintainability. Whether using built-in functions for quick calculations or creating user-defined functions for custom logic, understanding how to use them effectively is essential for any database developer.