SQL Functions | Scalar & Table-Valued Functions

Functions

Videos Discussion

In real-world databases, we often write the same logic again and again.

Examples:

Calculating tax on salary

Formatting names

Finding discount values

Returning filtered data repeatedly

Copy-pasting this logic across queries leads to:

Messy SQL

Hard maintenance

Higher chances of bugs

This is where User Defined Functions (UDFs) become useful. Functions allow us to wrap logic into a reusable unit that behaves like a built-in SQL function.

What Is a User Defined Function?

A User Defined Function is:

A database object

Created by developers

Accepts input parameters

Always returns a value

Can be used inside SQL queries

In simple words:

A function is reusable SQL logic that returns a result.

Types of User Defined Functions

SQL mainly provides two types of UDFs:

1. Scalar Functions
2. Table-Valued Functions

Scalar Functions

A scalar function:

Returns a single value

Works like built-in functions such as UPPER() or LEN()

Can be used in SELECT, WHERE, ORDER BY, etc.

When to Use Scalar Functions

Scalar functions are used when:

You need a calculated value

Logic applies to each row

Output is a single value

Examples:

Calculate bonus

Format strings

Convert values

Example: Calculate Annual Salary

CREATE FUNCTION calculate_annual_salary (@monthly_salary INT)
RETURNS INT
AS
BEGIN
    RETURN @monthly_salary * 12;
END;

Using the Scalar Function

SELECT emp_name,
       dbo.calculate_annual_salary(salary) AS annual_salary
FROM employees;

The function behaves like a built-in SQL function.

Key Characteristics of Scalar Functions

Return only one value

Cannot modify data

Can be nested inside queries

Improve code readability

Table-Valued Functions (TVFs)

Unlike scalar functions, table-valued functions return a table. They behave like a virtual table that can be queried.

When to Use Table-Valued Functions

TVFs are useful when:

You want reusable SELECT logic

Multiple rows are returned

Data needs to be filtered dynamically

Types of Table-Valued Functions

There are two kinds:

Inline Table-Valued Functions

Multi-Statement Table-Valued Functions

Inline Table-Valued Function

An inline TVF:

Contains a single SELECT statement

Performs better

Looks like a parameterized view

Example: Employees by Department

CREATE FUNCTION get_employees_by_department (@dept_id INT)
RETURNS TABLE
AS
RETURN
(
    SELECT emp_id, emp_name, salary
    FROM employees
    WHERE department_id = @dept_id
);

Using Inline TVF

SELECT *
FROM dbo.get_employees_by_department(10);

Multi-Statement Table-Valued Function

This type:

Contains multiple SQL statements

Uses a table variable

Slower but more flexible

Example: Salary Categorization

CREATE FUNCTION employee_salary_level ()
RETURNS @salary_table TABLE
(
    emp_name VARCHAR(50),
    salary_level VARCHAR(20)
)
AS
BEGIN
    INSERT INTO @salary_table
    SELECT emp_name,
           CASE 
               WHEN salary >= 80000 THEN 'High'
               WHEN salary >= 40000 THEN 'Medium'
               ELSE 'Low'
           END
    FROM employees;

    RETURN;
END;

Using Multi Statement TVF

SELECT *
FROM dbo.employee_salary_level();

Difference Between Scalar and Table Valued Functions

Feature	Scalar Function	Table Valued Function
Returns	Single value	Table
Usage	SELECT, WHERE	FROM clause
Performance	Can be slow	Inline is fastest
Use case	Calculations	Reusable queries

Performance Considerations

Avoid heavy logic inside scalar functions

Prefer inline TVFs over multi-statement TVFs

Do not use functions inside large WHERE clauses unnecessarily

Poorly designed functions can slow queries.

Common Real-World Use Cases

Salary calculations

Data formatting

Business rule validation

Reusable filters

Reporting logic

Difference Between Procedure and Function

Feature	Stored Procedure	Function
Returns value	Optional	Mandatory
Used in SELECT	No	Yes
Can modify data	Yes	No
Reusability	High	Very High

Functions are read only helpers, while procedures handle actions.

Two Minute Drill

Functions return values
Scalar functions return one value
Table valued functions return tables
Functions cannot modify data
Inline TVFs are best for performance