What Is a User-Defined Function?

UDFs are particularly useful when you need the same calculation across multiple queries. Instead of repeating complex logic, you define it once and reference it by name — just like built-in functions such as GETDATE() or SUM().

-- Create a scalar function
CREATE FUNCTION dbo.CalculateAge(@birth_date DATE)
RETURNS INT
AS
BEGIN
    DECLARE @age INT;
    SET @age = DATEDIFF(YEAR, @birth_date, GETDATE())
             - CASE
                 WHEN DATEADD(YEAR, DATEDIFF(YEAR, @birth_date, GETDATE()), @birth_date) > GETDATE()
                 THEN 1 ELSE 0
               END;
    RETURN @age;
END;

-- Use it in a query
SELECT name, birth_date, dbo.CalculateAge(birth_date) AS age
FROM employees;

Scalar Functions

Scalar functions return a single value. They can be used anywhere an expression is valid — SELECT lists, WHERE clauses, CHECK constraints, and computed columns.

CREATE FUNCTION dbo.FormatPhoneNumber(@phone NVARCHAR(20))
RETURNS NVARCHAR(20)
AS
BEGIN
    DECLARE @digits NVARCHAR(20) = REPLACE(REPLACE(REPLACE(@phone, '-', ''), '(', ''), ')', '');

    IF LEN(@digits) = 10
        RETURN '(' + SUBSTRING(@digits, 1, 3) + ') '
                  + SUBSTRING(@digits, 4, 3) + '-'
                  + SUBSTRING(@digits, 7, 4);
    ELSE IF LEN(@digits) = 11
        RETURN '+' + SUBSTRING(@digits, 1, 1) + ' ('
                  + SUBSTRING(@digits, 2, 3) + ') '
                  + SUBSTRING(@digits, 5, 3) + '-'
                  + SUBSTRING(@digits, 8, 4);
    ELSE
        RETURN @phone;
END;

-- Use in queries
SELECT name, dbo.FormatPhoneNumber(phone) AS formatted_phone
FROM contacts;

Inline Table-Valued Functions

CREATE FUNCTION dbo.GetOrdersByDateRange(@start_date DATE, @end_date DATE)
RETURNS TABLE
AS
RETURN
(
    SELECT order_id, customer_id, order_date, total, status
    FROM orders
    WHERE order_date BETWEEN @start_date AND @end_date
);

-- Use like a table
SELECT * FROM dbo.GetOrdersByDateRange('2024-01-01', '2024-06-30');

-- Join with other tables
SELECT c.name, o.order_id, o.total
FROM customers c
INNER JOIN dbo.GetOrdersByDateRange('2024-01-01', '2024-12-31') o
    ON c.customer_id = o.customer_id;

Multi-Statement Table-Valued Functions

CREATE FUNCTION dbo.GetSalesReport(@year INT)
RETURNS @report TABLE
(
    month_name NVARCHAR(15),
    total_orders INT,
    total_revenue DECIMAL(12,2),
    avg_order_value DECIMAL(10,2)
)
AS
BEGIN
    INSERT INTO @report
    SELECT
        DATENAME(MONTH, order_date) AS month_name,
        COUNT(*) AS total_orders,
        ISNULL(SUM(total), 0) AS total_revenue,
        ISNULL(AVG(total), 0) AS avg_order_value
    FROM orders
    WHERE YEAR(order_date) = @year
    GROUP BY MONTH(order_date), DATENAME(MONTH, order_date)
    ORDER BY MONTH(order_date);

    RETURN;
END;

-- Use the report
SELECT * FROM dbo.GetSalesReport(2024);

Deterministic vs Non-Deterministic Functions

-- Deterministic: always returns the same result for the same input
CREATE FUNCTION dbo.FahrenheitToCelsius(@fahrenheit DECIMAL(5,2))
RETURNS DECIMAL(5,2)
AS
BEGIN
    RETURN ROUND((@fahrenheit - 32) * 5.0 / 9.0, 2);
END;

-- Non-deterministic: uses GETDATE(), so output varies
CREATE FUNCTION dbo.GetBusinessDayStatus()
RETURNS NVARCHAR(10)
AS
BEGIN
    IF DATEPART(WEEKDAY, GETDATE()) IN (1, 7)
        RETURN 'WEEKEND';
    ELSE
        RETURN 'WEEKDAY';
END;

Functions vs Stored Procedures

-- Function: pure calculation, no side effects
CREATE FUNCTION dbo.CalculateDiscount(@total DECIMAL(10,2), @tier NVARCHAR(20))
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN CASE @tier
        WHEN 'GOLD' THEN @total * 0.20
        WHEN 'SILVER' THEN @total * 0.10
        WHEN 'BRONZE' THEN @total * 0.05
        ELSE 0
    END;
END;

-- Stored procedure: modifies data
CREATE PROCEDURE ApplyDiscount
    @order_id INT,
    @tier NVARCHAR(20)
AS
BEGIN
    DECLARE @total DECIMAL(10,2);
    SELECT @total = total FROM orders WHERE order_id = @order_id;

    UPDATE orders
    SET discount = dbo.CalculateDiscount(@total, @tier),
        total = total - dbo.CalculateDiscount(@total, @tier)
    WHERE order_id = @order_id;
END;

Performance Considerations

-- Slow: scalar function called per row
SELECT name, dbo.CalculateAge(birth_date) AS age
FROM employees; -- executes function once per row

-- Faster: inline TVF with set-based logic
CREATE FUNCTION dbo.GetEmployeesByAgeRange(@min_age INT, @max_age INT)
RETURNS TABLE
AS
RETURN (
    SELECT employee_id, name, birth_date,
           DATEDIFF(YEAR, birth_date, GETDATE()) AS age
    FROM employees
    WHERE DATEDIFF(YEAR, birth_date, GETDATE()) BETWEEN @min_age AND @max_age
);

SELECT * FROM dbo.GetEmployeesByAgeRange(25, 35); -- single execution

Common Patterns

-- Pattern: Parameterized view (inline TVF)
CREATE FUNCTION dbo.SearchEmployees(@department NVARCHAR(50), @min_salary DECIMAL(10,2))
RETURNS TABLE
AS
RETURN (
    SELECT e.employee_id, e.name, e.salary, d.department_name
    FROM employees e
    INNER JOIN departments d ON e.department_id = d.department_id
    WHERE (@department IS NULL OR d.department_name = @department)
      AND e.salary >= @min_salary
);

-- Pattern: Lookup function
CREATE FUNCTION dbo.GetDepartmentName(@dept_id INT)
RETURNS NVARCHAR(50)
AS
BEGIN
    DECLARE @name NVARCHAR(50);
    SELECT @name = department_name FROM departments WHERE department_id = @dept_id;
    RETURN ISNULL(@name, 'Unknown');
END;

-- Use in SELECT
SELECT name, dbo.GetDepartmentName(department_id) AS department
FROM employees;