T-SQL User-Defined Function

Master T-SQL user-defined functions — scalar functions, inline table-valued functions (iTVFs), and multi-statement table-valued functions (MTVFs). Covers performance differences, scalar UDF inlining (SQL Server 2019), WITH SCHEMABINDING, comparison with stored procedures, and when to choose each type.

A user-defined function (UDF) is a named T-SQL routine that takes parameters and returns a value — like a stored procedure, but designed to be used inside expressions: in a SELECT list, a WHERE clause, a computed column, or wherever a value (or a table) can appear.

T-SQL has three flavors of UDF, distinguished by what they return and how they're written:

A scalar function returns a single value of a specified type. Use them to wrap business logic that produces one value — calculations, formatting, lookups.

Syntax

CREATE OR ALTER FUNCTION dbo.GetSquare (@number INT)
RETURNS INT
AS
BEGIN
    DECLARE @square INT;
    SET @square = @number * @number;
    RETURN @square;
END;
GO

SELECT dbo.GetSquare(5) AS SquareOf5;

A more realistic scalar function — computes age from a date of birth, accounting for whether the birthday has happened this year.

CREATE TABLE Employees (
    id              INT PRIMARY KEY,
    emp_name        VARCHAR(30),
    salary          INT,
    department_id   INT,
    dob             DATE
);

INSERT INTO Employees VALUES
    (1,  'John Doe',      50000, 1, '1985-03-15'),
    (2,  'Jane Smith',    60000, 2, '1979-07-22'),
    (3,  'Alice Johnson', 55000, 1, '1990-05-10'),
    (4,  'Bob Brown',     52000, 3, '1988-11-30'),
    (5,  'Eve Brown',     48000, 2, '1995-02-18');

CREATE OR ALTER FUNCTION dbo.GetEmployeeAge (@DOB DATE)
RETURNS INT
AS
BEGIN
    DECLARE @age INT;

    -- Years between today and DOB
    SET @age = DATEDIFF(YEAR, @DOB, GETDATE());

    -- If birthday hasn't happened yet this year, subtract 1
    IF (MONTH(@DOB) > MONTH(GETDATE()))
       OR (MONTH(@DOB) = MONTH(GETDATE()) AND DAY(@DOB) > DAY(GETDATE()))
    BEGIN
        SET @age = @age - 1;
    END

    RETURN @age;
END;
GO

SELECT
    emp_name,
    dob,
    dbo.GetEmployeeAge(dob) AS age
FROM Employees;

An iTVF returns a table. The body is a single SELECT statement. The optimizer treats it just like a parameterized view — fully inlines it into the calling query, applies indexes and statistics, picks the best plan. iTVFs are the fastest way to encapsulate a parameterized query.

Syntax

Note the lack of BEGIN/END — an iTVF is just RETURN (single_select).

CREATE TABLE Orders (
    OrderID     INT PRIMARY KEY,
    CustomerID  INT,
    OrderDate   DATE,
    Amount      DECIMAL(10, 2)
);

INSERT INTO Orders VALUES
    (1, 1, '2024-01-01', 100.00),
    (2, 2, '2024-02-01', 200.00),
    (3, 1, '2024-03-01', 150.00);

CREATE OR ALTER FUNCTION dbo.GetOrdersByCustomer (@CustomerID INT)
RETURNS TABLE
AS
RETURN
(
    SELECT OrderID, OrderDate, Amount
    FROM   Orders
    WHERE  CustomerID = @CustomerID
);
GO

Use it just like a table — including in JOINs:

SELECT * FROM dbo.GetOrdersByCustomer(1);

An MTVF also returns a table — but the body is a multi-statement block that populates a declared TABLE variable. Use it when the result needs multiple steps that can't fit in a single SELECT.

Syntax

DROP TABLE IF EXISTS Employees;

CREATE TABLE Employees (
    EmployeeID    INT PRIMARY KEY,
    FirstName     NVARCHAR(50),
    LastName      NVARCHAR(50),
    Salary        MONEY,
    DepartmentID  INT
);

INSERT INTO Employees VALUES
    (1, 'John', 'Doe',   45000, 1),
    (2, 'Jane', 'Smith', 55000, 1),
    (3, 'Jim',  'Brown', 30000, 2),
    (4, 'Jake', 'White', 70000, 2),
    (5, 'Jill', 'Green', 48000, 1);

CREATE OR ALTER FUNCTION dbo.GetEmployeesByDepartment (@DepartmentID INT)
RETURNS @EmployeeTable TABLE (
    EmployeeID  INT,
    FirstName   NVARCHAR(50),
    LastName    NVARCHAR(50),
    Salary      MONEY
)
AS
BEGIN
    -- Step 1: Pull the matching employees
    INSERT INTO @EmployeeTable (EmployeeID, FirstName, LastName, Salary)
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM   Employees
    WHERE  DepartmentID = @DepartmentID;

    -- Step 2: Give a 10% bonus to anyone earning under 50,000
    UPDATE @EmployeeTable
    SET    Salary = Salary * 1.10
    WHERE  Salary < 50000;

    RETURN;
END;
GO

SELECT * FROM dbo.GetEmployeesByDepartment(1);

Notice rows 1 and 5 got the 10% bump (their original salaries 45000 and 48000 were below 50000); Jane's stayed at 55000.

CREATE OR ALTER FUNCTION dbo.GetEmployeesByDepartment_inline (@DepartmentID INT)
RETURNS TABLE
AS
RETURN
(
    SELECT EmployeeID, FirstName, LastName,
           CASE WHEN Salary < 50000 THEN Salary * 1.10 ELSE Salary END AS Salary
    FROM   Employees
    WHERE  DepartmentID = @DepartmentID
);

DROP FUNCTION IF EXISTS dbo.GetSquare;
DROP FUNCTION IF EXISTS dbo.GetOrdersByCustomer;
DROP FUNCTION IF EXISTS dbo.GetEmployeesByDepartment;

1. Prefer iTVFs. Whenever you need to encapsulate a parameterized query, write it as an iTVF — fastest, most flexible, optimizer-friendly.
2. Use scalar UDFs sparingly, especially on SQL Server pre-2019. Replace with inline expressions where possible.
3. Always include the schema — dbo.fn_name(...), never just fn_name(...).
4. Use WITH SCHEMABINDING on scalar UDFs that don't need underlying-table flexibility — enables 2019+ inlining and stops accidental table changes that would break the function.
5. Use CREATE OR ALTER FUNCTION for idempotent deployment.
6. Don't reach for an MTVF just because you have multiple lines of work — almost every MTVF can be rewritten as an iTVF with CASE expressions and CTEs.

• T-SQL has three function flavors: scalar (returns one value), inline TVF (single SELECT, optimizer-friendly), and multi-statement TVF (table variable + multiple steps).
• Functions go inside expressions (SELECT, WHERE, JOIN); procedures are called via EXEC.
• Functions cannot modify data — no INSERT/UPDATE/DELETE inside. If you need that, write a procedure.
• Always qualify with the schema (dbo.fn_name) when calling — unqualified names are looked up as built-ins.
• Performance: iTVF > inline expression > scalar UDF (with 2019+ inlining) > MTVF > scalar UDF (pre-2019). Pick the lightest tool for the job.
• Use WITH SCHEMABINDING for scalar UDFs to enable inlining; use CREATE OR ALTER FUNCTION for clean deployments.