What are Functions in SQL Server?

Imagine you have a calculator.

Whenever you want to add two numbers, you simply press the + button
instead of writing the entire addition logic every time.

A Function in SQL Server works exactly like that.

It is a reusable block of SQL code that:

 Accepts input values (optional)

 Performs some processing

 Returns a value or a table.

Instead of writing the same logic repeatedly, we create a function once

and use it whenever needed.

Real-Life Example

Imagine a bank.

Whenever a customer visits the branch, the employee needs to know

whether the customer is:

 Premium Customer

 Regular Customer

Instead of checking the customer's balance every single time, we create a

function.

Logic:

Customer Balance

 Balance >= ₹5,00,000 → Premium Customer

 Otherwise → Regular Customer

Now every report can simply call this function instead of writing the logic
repeatedly.

Types of User Defined Functions (UDFs)

There are mainly two types of User Defined Functions in SQL Server:

1. Scalar Function

2. Table-Valued Function
Scalar Function

Returns only one value.

Table-Valued Function

Returns an entire table.

1. Scalar Function

A Scalar Function always returns a single value.

Examples

 Annual Salary

 Bonus

 Tax

 Age

 Full Name

 Customer Category

Only one value is returned.

Syntax:

Create Function FunctionName

@Parameter DataType

Returns ReturnDataType

As

Begin

----Logic

Return value;

End;

Simple Example:

Create function fn_AddTen

(

@Number int

Returns int

As

Begin

return @Number +10;

End;

---Execute the function

Select dbo.fn_AddTen(20) as Result

Sample Employee Table

CREATE TABLE Employees

EmpId INT,

EmpName VARCHAR(30),

Salary INT

);

INSERT INTO Employees

VALUES

(101,'Rahul',45000),

(102,'Amit',70000),

(103,'Neha',55000),

(104,'Priya',80000);

Output

EmpI EmpNa Salar

d me y

101 Rahul 4500

EmpI EmpNa Salar
d me y

7000
102 Amit
0

5500
103 Neha
0

8000
104 Priya
0

Example 1 - Calculate Annual Salary

Suppose every report requires Annual Salary.

Instead of writing:

Salary * 12

every time, we create a function.

Create Function

CREATE FUNCTION fnAnnualSalary

@MonthlySalary INT

RETURNS INT

AS

BEGIN

RETURN @MonthlySalary * 12

END;

Execute Function

SELECT EmpName,Salary,[Link](Salary) AS AnnualSalary

FROM Employees;

Output
EmpNa Salar AnnualSal
me y ary

4500
Rahul 540000
0

7000
Amit 840000
0

5500
Neha 660000
0

8000
Priya 960000
0

Example 2 - Employee Category

Suppose HR wants to categorize employees.

Business Rule:

 Salary >= 60000 → High Salary

 Otherwise → Normal Salary

Create Function

CREATE FUNCTION fnEmployeeCategory

@Salary INT

RETURNS VARCHAR(20)

AS

BEGIN

DECLARE @Category VARCHAR(20)

IF @Salary >= 60000

SET @Category='High Salary'

ELSE

SET @Category='Normal Salary'

 RETURN @Category

END;

Execute

SELECT EmpName,Salary, [Link](Salary) AS Category

FROM Employees;

Output

EmpNa Salar
Category
me y

4500 Normal
Rahul
0 Salary

7000
Amit High Salary
0

5500 Normal
Neha
0 Salary

8000
Priya High Salary
0

Table-Valued Function (TVF)

Unlike a Scalar Function,

A Table-Valued Function returns an entire table.

Think of it as a reusable SELECT query.

Whenever HR wants employees earning above a certain salary, instead of

writing the same SELECT statement repeatedly, we can create a Table-
Valued Function.

Syntax

CREATE FUNCTION FunctionName()

RETURNS TABLE

AS
RETURN

SELECT ...

);

Example 1 - High Salary Employees

Create Function

CREATE FUNCTION fnHighSalaryEmployees

@Salary INT

RETURNS TABLE

AS

RETURN

SELECT *

FROM Employees

WHERE Salary > @Salary

);

Execute

SELECT *

FROM [Link](50000);

Output

EmpI EmpNa Salar

d me y

7000
102 Amit
0

103 Neha 5500

EmpI EmpNa Salar
d me y

8000
104 Priya
0

Execute Again

SELECT *

FROM [Link](75000);

Output

EmpI EmpNa Salar

d me y

8000
104 Priya
0

Example 2 - Banking Domain

Create Table

CREATE TABLE Customers

CustomerId INT,

CustomerName VARCHAR(30),

Balance MONEY

);

INSERT INTO Customers

VALUES

(1,'Rohan',25000),

(2,'Anita',85000),

(3,'Vikas',120000),

(4,'Sneha',45000);
Create Function

CREATE FUNCTION fnCustomersByBalance

@Balance MONEY

RETURNS TABLE

AS

RETURN

SELECT *

FROM Customers

WHERE Balance >= @Balance

);

Execute

SELECT *

FROM [Link](50000);

Output

Customer CustomerNa Balanc

Id me e

2 Anita 85000

3 Vikas 120000

Difference Between Scalar Function and Table-Valued Function

Feature Scalar Function Table-Valued Function

Returns Single Value Table

INT, VARCHAR, DATE, MONEY

RETURNS TABLE
etc.
Feature Scalar Function Table-Valued Function

Can be used
SELECT, WHERE, ORDER BY FROM clause
in

Return High Salary

Example Calculate Bonus
Employees

How to Call a Scalar Function

SELECT [Link](45000);

Output

540000

Or

SELECT

EmpName,

[Link](Salary)

FROM Employees;

How to Call a Table-Valued Function

SELECT *

FROM [Link](50000);

When Should We Use Functions?

Use a Scalar Function when:

 You need to return only one value.

 Examples:

o Tax Calculation

o Bonus

o Annual Salary

o Age

o Customer Category
Use a Table-Valued Function when:

 You need to return multiple rows.

 Examples:

o Employees by Department

o Customers above a Balance

o Orders between two Dates

o Students scoring above a certain percentage

Interview Questions

Q1. What is a Function in SQL Server?

A Function is a reusable database object that accepts input parameters,

performs some processing, and always returns either a single value
(Scalar Function) or a table (Table-Valued Function).

Q2. What are the types of User Defined Functions?

There are two commonly used types:

1. Scalar Function

2. Table-Valued Function

Q3. What is the difference between a Scalar Function and a Table-

Valued Function?

 Scalar Function returns only one value.

 Table-Valued Function returns an entire table.

Q4. Can a Function return multiple values?

No.

A Scalar Function returns only one value.

If multiple rows are required, use a Table-Valued Function.

Q5. Where can a Scalar Function be used?

It can be used inside:

 SELECT

 WHERE

 ORDER BY

 HAVING

just like any built-in SQL Server function.

Q6. What is the difference between a Stored Procedure and a

Function?

Stored Procedure

 Can return zero, one, or multiple result sets.

 Can perform INSERT, UPDATE, DELETE operations.

 Executed using the EXEC command.

Function

 Must always return either a value or a table.

 Cannot modify data using INSERT, UPDATE, or DELETE (under

standard SQL Server UDF restrictions).

 Can be called directly inside SQL queries such as SELECT and

WHERE.