SQL Database Functions - MSDN Documentation

Introduction

SQL functions are database objects that contain a set of Transact-SQL statements to perform a specific task. Functions return a value to the caller. They can simplify code, promote reusability, and enforce business logic consistently across your database. Unlike stored procedures, functions can be used directly within SQL statements (like SELECT, WHERE, etc.) and must return a value.

Types of Functions

SQL Server supports several types of functions:

Scalar Functions: Return a single data value.
Table-Valued Functions (TVFs): Return a result set that can be treated as a table. These are further divided into Inline TVFs and Multi-Statement TVFs.

Scalar Functions

Scalar functions accept parameters and return a single value of a specified data type. They are useful for calculations or formatting data.

Example: Calculating Age

This function calculates the age of a person based on their date of birth.

                    
CREATE FUNCTION dbo.CalculateAge (@DateOfBirth DATE)
RETURNS INT
AS
BEGIN
    DECLARE @Age INT;
    SET @Age = DATEDIFF(year, @DateOfBirth, GETDATE());
    IF (MONTH(@DateOfBirth) > MONTH(GETDATE())) OR (MONTH(@DateOfBirth) = MONTH(GETDATE()) AND DAY(@DateOfBirth) > DAY(GETDATE()))
    BEGIN
        SET @Age = @Age - 1;
    END
    RETURN @Age;
END;
                    
                

Usage:


SELECT dbo.CalculateAge('1990-05-15') AS Age;

Table-Valued Functions (TVFs)

TVFs return a table result set. They are powerful for encapsulating complex queries and making them reusable.

Inline Table-Valued Functions (ITVF)

ITVFs are more efficient as they are expanded into the calling query. They have a single SELECT statement in their body.

Example: Get Employees by Department

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

Usage:

                    
SELECT EmployeeID, FirstName, LastName
FROM dbo.GetEmployeesByDepartment(3)
WHERE LastName LIKE 'S%';

Multi-Statement Table-Valued Functions (MSTVF)

MSTVFs allow for more complex logic using multiple Transact-SQL statements to populate the returned table. They are generally less performant than ITVFs.

Example: Get Active Employees in a Department

                    
CREATE FUNCTION dbo.GetActiveEmployeesInDepartment (@DepartmentID INT)
RETURNS @ActiveEmployees TABLE
(
    EmployeeID INT PRIMARY KEY,
    FullName VARCHAR(100),
    HireDate DATE
)
AS
BEGIN
    INSERT INTO @ActiveEmployees (EmployeeID, FullName, HireDate)
    SELECT EmployeeID, FirstName + ' ' + LastName, HireDate
    FROM Employees
    WHERE DepartmentID = @DepartmentID AND IsActive = 1;

    RETURN;
END;
                    
                

Usage:

                    
SELECT FullName, HireDate
FROM dbo.GetActiveEmployeesInDepartment(5);

Key Considerations

Functions cannot perform DML operations (INSERT, UPDATE, DELETE) on database tables unless they are part of a specific context like `OPENQUERY` or have specific permissions.
Scalar functions can impact performance if called for every row in a large query. Consider using table-valued functions or other methods.
ITVFs are generally preferred over MSTVFs due to performance advantages.
Functions can improve code maintainability and consistency.

Commonly Used Built-in Functions

SQL Server provides a rich set of built-in functions for various purposes:

SUM() Calculates the sum of values in a column.
AVG() Calculates the average of values in a column.
COUNT() Counts the number of rows or non-null values.
MAX() Finds the maximum value in a column.
MIN() Finds the minimum value in a column.
GETDATE() Returns the current database system date and time.
LEN() Returns the length of a string expression.
SUBSTRING() Extracts a part of a string.
CAST() / CONVERT() Converts an expression from one data type to another.