User-Defined Functions (Transact-SQL)

User-defined functions (UDFs) in SQL Server allow you to create custom logic that can be reused in your Transact-SQL queries and other code. UDFs can accept input parameters, perform computations or data manipulations, and return a scalar value or a table.

Types of User-Defined Functions

SQL Server supports three main types of user-defined functions:

1. Scalar Functions

Scalar functions return a single data value. They are useful for encapsulating complex calculations or data transformations that result in a single output.

CREATE FUNCTION dbo.GetEmployeeFullName (@EmployeeID INT)
RETURNS NVARCHAR(100)
AS
BEGIN
    DECLARE @FullName NVARCHAR(100);
    SELECT @FullName = FirstName + ' ' + LastName
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
    RETURN @FullName;
END;

2. Table-Valued Functions (TVFs)

Table-valued functions return a result set in the form of a table. They are highly flexible and can be used wherever a table or view can be referenced.

Inline Table-Valued Functions (ITVFs)

ITVFs are simpler and generally perform better than multi-statement TVFs. They have a single `RETURN` statement.

CREATE FUNCTION dbo.GetEmployeesByDepartment (@DepartmentName NVARCHAR(50))
RETURNS TABLE
AS
RETURN
(
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Department = @DepartmentName
);

Multi-Statement Table-Valued Functions (MSTVFs)

MSTVFs allow for more complex logic, including multiple T-SQL statements, to populate the returned table. They are less performant than ITVFs.

CREATE FUNCTION dbo.GetHighEarners (@MinSalary DECIMAL(18, 2))
RETURNS @HighEarnerTable TABLE
(
    EmployeeID INT PRIMARY KEY,
    FullName NVARCHAR(100),
    Salary DECIMAL(18, 2)
)
AS
BEGIN
    INSERT INTO @HighEarnerTable (EmployeeID, FullName, Salary)
    SELECT
        e.EmployeeID,
        e.FirstName + ' ' + e.LastName,
        e.Salary
    FROM
        Employees e
    WHERE
        e.Salary >= @MinSalary
        AND EXISTS (SELECT 1 FROM Orders o WHERE o.EmployeeID = e.EmployeeID); -- Example: Only include employees who have placed orders

    RETURN;
END;

Creating User-Defined Functions

The general syntax for creating a UDF is:

CREATE FUNCTION [schema_name.]function_name
( [ { @parameter_name data_type [ = default ] [ OUT | OUTPUT ] } [ ,...n ] ] )
RETURNS return_data_type
[AS]

Or for table-valued functions:

CREATE FUNCTION [schema_name.]function_name
( [ { @parameter_name data_type [ = default ] [ OUT | OUTPUT ] } [ ,...n ] ] )
RETURNS TABLE
[AS]
RETURN
( table_return_expression )

Using User-Defined Functions

You can invoke scalar functions directly in expressions:

SELECT
    dbo.GetEmployeeFullName(EmployeeID) AS FullName,
    Salary
FROM
    Employees
WHERE
    Salary > 50000;

Table-valued functions can be used in the FROM clause of a query, similar to a table or view:

SELECT
    e.FullName,
    e.Salary
FROM
    dbo.GetEmployeesByDepartment('Sales') AS e
WHERE
    e.Salary < 60000;

SELECT
    h.FullName,
    h.Salary
FROM
    dbo.GetHighEarners(75000) AS h;

Considerations

Performance: Scalar UDFs can sometimes degrade query performance due to row-by-row processing. ITVFs are generally preferred over MSTVFs for performance.
Determinism: Functions can be deterministic (always return the same result for the same input) or non-deterministic.
Side Effects: Functions should ideally not have side effects (e.g., modifying data).
Nesting: UDFs can call other UDFs, but be mindful of potential performance impacts and recursion limits.

Important: Always test UDFs thoroughly, especially in performance-critical scenarios, and consider alternatives like inline views or Common Table Expressions (CTEs) if performance becomes an issue.

Microsoft SQL Server Documentation