Writing Functions in Microsoft SQL Server

User-defined functions (UDFs) in SQL Server allow you to encapsulate business logic that can be reused across multiple queries and stored procedures. Functions help reduce code duplication and improve maintainability.

Types of Functions

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

• Scalar Functions: Return a single value (e.g., int, varchar, datetime)
• Table-Valued Functions: Return a table result set

Creating a Scalar Function

A scalar function is useful when you need to compute and return a single value based on input parameters.

CREATE FUNCTION dbo.CalculateAgeInYears (@BirthDate DATETIME)
RETURNS INT
AS
BEGIN
    DECLARE @Age INT;
    SET @Age = DATEDIFF(YEAR, @BirthDate, GETDATE());
    RETURN @Age;
END;

You can then use this function in queries:

SELECT 
    CustomerId,
    CustomerName,
    dbo.CalculateAgeInYears(BirthDate) AS Age
FROM Customers;

Creating an Inline Table-Valued Function

Inline table-valued functions return a table and are often more efficient than multi-statement functions.

CREATE FUNCTION dbo.GetCustomerOrders (@CustomerId INT)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        OrderId,
        OrderDate,
        OrderTotal
    FROM Orders
    WHERE CustomerId = @CustomerId
);

Usage:

SELECT *
FROM dbo.GetCustomerOrders(5);

Multi-Statement Table-Valued Functions

For more complex logic, you can use multi-statement table-valued functions (MSTVF):

CREATE FUNCTION dbo.GetOrderSummary (@StartDate DATETIME)
RETURNS @OrderSummary TABLE 
(
    CustomerId INT,
    TotalOrders INT,
    TotalSpent DECIMAL(10,2)
)
AS
BEGIN
    INSERT INTO @OrderSummary
    SELECT 
        CustomerId,
        COUNT(*) AS TotalOrders,
        SUM(OrderTotal) AS TotalSpent
    FROM Orders
    WHERE OrderDate >= @StartDate
    GROUP BY CustomerId;

    RETURN;
END;

Important Considerations

• Performance: Inline table-valued functions are generally faster than MSTVFs because they can be inlined into the execution plan.
• Deterministic Functions: Use the SCHEMABINDING option to improve optimization when your function is deterministic.
• Non-CLR vs CLR: Non-CLR functions (T-SQL) are fine for most scenarios, but CLR functions can be better for complex logic.
• Schema Qualification: Always prefix functions with the schema name (typically dbo.) when calling them.

Altering and Dropping Functions

To modify a function, use ALTER FUNCTION. To remove it, use DROP FUNCTION:

-- Modify a function
ALTER FUNCTION dbo.CalculateAgeInYears (@BirthDate DATETIME)
RETURNS INT
AS
BEGIN
    DECLARE @Age INT;
    SET @Age = DATEDIFF(YEAR, @BirthDate, GETDATE());
    IF @Age < 0 SET @Age = 0;
    RETURN @Age;
END;

-- Drop a function
DROP FUNCTION dbo.CalculateAgeInYears;

Best Practices

• Use functions for reusable logic and calculations
• Prefer inline table-valued functions for better performance
• Always use SCHEMABINDING for deterministic functions to prevent breaking changes
• Keep functions focused on a single responsibility
• Document function parameters and return values with comments