SQL Topics
Functions in SQL
title: Functions in SQL
When working with databases, developers often need to perform calculations, transformations, validations, and data processing repeatedly.
Examples:
- Calculate employee bonuses
- Convert text to uppercase
- Find average salaries
- Calculate total sales
- Format dates and times
Writing the same logic repeatedly can make SQL code difficult to maintain.
To solve this problem, SQL provides Functions.
Functions allow developers to encapsulate reusable logic and return a value or result whenever needed.
Just as programming languages use functions to organize code, SQL uses functions to simplify and standardize database operations.
What is a Function?
A Function is a reusable database object that accepts input values, performs operations, and returns a result.
Think of a function as:
Example:
ABS(-10)Input:
-10Output:
10The function performs a calculation and returns a value.
Why Are Functions Important?
Suppose an HR system repeatedly calculates:
Annual Salary
Tax Amount
Employee BonusWithout functions:
Same Logic Repeated
Many TimesWith functions:
SELECT CalculateBonus(Salary);The logic is written once and reused everywhere.
Types of SQL Functions
Functions are generally divided into:
Built-In Functions
User-Defined Functions (UDFs)Built-In Functions
Built-in functions are provided by the database system.
Examples:
AVG()
COUNT()
SUM()
MAX()
MIN()
UPPER()
LOWER()
NOW()No creation is required.
They are available automatically.
User-Defined Functions (UDFs)
User-defined functions are created by developers.
Example:
CREATE FUNCTION CalculateBonus()They help implement custom business logic.
Function Categories
SQL functions are commonly categorized as:
Scalar Functions
Aggregate Functions
String Functions
Date Functions
Mathematical Functions
User-Defined Functions
Table-Valued FunctionsScalar Functions
Scalar functions return a single value.
Example:
UPPER('rahul')Output:
RAHULAnother example:
ABS(-50)Output:
50Aggregate Functions
Aggregate functions operate on multiple rows and return a single result.
Examples:
AVG()
SUM()
COUNT()
MAX()
MIN()Example:
SELECT AVG(Salary)
FROM Employees;Output:
65000String Functions
Used for text manipulation.
Examples:
UPPER()
LOWER()
LENGTH()
TRIM()
CONCAT()
SUBSTRING()Example:
SELECT UPPER('sql');Result:
SQLDate Functions
Used for working with dates and times.
Examples:
NOW()
CURDATE()
YEAR()
MONTH()
DAY()
DATEDIFF()Example:
SELECT YEAR('2025-05-20');Result:
2025Mathematical Functions
Used for calculations.
Examples:
ABS()
ROUND()
CEIL()
FLOOR()
POWER()
SQRT()Example:
SELECT ROUND(15.678, 2);Result:
15.68User-Defined Functions (UDF)
Developers can create custom functions.
SQL Server Example:
CREATE FUNCTION SquareNumber
(
@Number INT
)
RETURNS INT
AS
BEGIN
RETURN @Number * @Number;
END;Executing a User-Defined Function
Example:
SELECT dbo.SquareNumber(5);Result:
25Function Syntax
General syntax:
CREATE FUNCTION FunctionName
(
Parameters
)
RETURNS DataType
AS
BEGIN
Logic
RETURN Value
END;Understanding Function Components
Function Name
Unique function identifier.
Example:
CalculateTaxParameters
Input values.
Example:
@SalaryRETURNS
Defines return type.
Example:
RETURNS DECIMAL(10,2)RETURN
Returns the final result.
Creating a Simple Function
Example:
CREATE FUNCTION DoubleValue
(
@Value INT
)
RETURNS INT
AS
BEGIN
RETURN @Value * 2;
END;Execution:
SELECT dbo.DoubleValue(10);Output:
20Function with Multiple Parameters
Example:
CREATE FUNCTION AddNumbers
(
@Num1 INT,
@Num2 INT
)
RETURNS INT
AS
BEGIN
RETURN @Num1 + @Num2;
END;Execution:
SELECT dbo.AddNumbers(10,20);Output:
30Function Using Employee Salary
Example:
CREATE FUNCTION CalculateBonus
(
@Salary DECIMAL(10,2)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
RETURN @Salary * 0.10;
END;Execution:
SELECT
EmployeeName,
dbo.CalculateBonus(Salary)
FROM Employees;Table-Valued Functions (TVF)
Unlike scalar functions:
Scalar Function
ā
Returns One ValueTable-valued functions:
Return TableInline Table-Valued Function
Example:
CREATE FUNCTION GetEmployees()
RETURNS TABLE
AS
RETURN
(
SELECT *
FROM Employees
);Execution:
SELECT *
FROM GetEmployees();Returns an entire table.
Multi-Statement Table-Valued Function
Example:
CREATE FUNCTION GetHighSalaryEmployees()
RETURNS @Result TABLE
(
EmployeeID INT,
EmployeeName VARCHAR(100)
)
AS
BEGIN
INSERT INTO @Result
SELECT EmployeeID,
EmployeeName
FROM Employees
WHERE Salary > 60000;
RETURN;
END;Real-World Example: HR System
Function:
Calculate Bonus
Calculate Tax
Calculate Annual SalaryReusable throughout the application.
Real-World Example: Banking
Functions:
Interest Calculation
Loan Eligibility
Risk AssessmentUsed repeatedly.
Real-World Example: E-Commerce
Functions:
Discount Calculation
Shipping Cost
Reward PointsAutomate business logic.
Function vs Stored Procedure
Functions and Stored Procedures are often confused.
Function:
Must Return ValueStored Procedure:
May Or May Not Return ValueFunction:
Can Be Used
Inside SELECTExample:
SELECT CalculateBonus(Salary)
FROM Employees;Stored Procedure:
Cannot Be Used
Inside SELECTMust be executed separately.
Comparison Table
| Feature | Function | Stored Procedure |
|---|---|---|
| Returns Value | Yes | Optional |
| Use in SELECT | Yes | No |
| Parameters | Yes | Yes |
| Reusability | High | High |
| Data Modification | Limited | Full Support |
Advantages of Functions
Code Reusability
Write once, use many times.
Improved Maintainability
Business logic is centralized.
Consistent Calculations
Same logic everywhere.
Cleaner Queries
Reduces repeated expressions.
Better Productivity
Developers work faster.
Disadvantages of Functions
Performance Overhead
Complex functions can slow queries.
Database Dependency
Syntax varies by database.
Limited Data Modification
Functions usually cannot perform unrestricted INSERT, UPDATE, or DELETE operations.
Debugging Complexity
Large functions become difficult to maintain.
Performance Considerations
Functions are useful but excessive usage may:
Increase CPU Usage
Reduce Query Speedespecially:
Row-by-Row Function Callson large datasets.
Always test performance.
Common Errors
Missing RETURN Statement
Wrong:
CREATE FUNCTION Test()
RETURNS INT
AS
BEGIN
END;A return value is required.
Incorrect Return Type
Returned value must match:
RETURNSdata type.
Parameter Type Mismatch
Input values must match expected types.
Using Functions for Heavy Processing
Large calculations may impact performance.
Best Practices
Use Descriptive Names
Example:
CalculateBonus
GetEmployeeCount
CalculateTaxKeep Functions Focused
One purpose per function.
Validate Inputs
Handle invalid data.
Avoid Complex Logic
Break large functions into smaller ones.
Test Performance
Particularly on large datasets.
Common Interview Questions
What is a SQL Function?
A reusable database object that accepts inputs and returns a result.
What is the difference between a Function and a Stored Procedure?
Functions must return a value and can be used inside SELECT statements.
What are Scalar Functions?
Functions that return a single value.
What are Table-Valued Functions?
Functions that return a table.
Can Functions accept parameters?
Yes.
Input parameters are commonly used.
Summary
Functions are powerful SQL objects that encapsulate reusable logic and return values or result sets. They simplify calculations, improve code organization, and help standardize business logic across database applications.
In this lesson, you learned:
- What Functions are
- Built-in Functions
- User-Defined Functions
- Scalar Functions
- Aggregate Functions
- String Functions
- Date Functions
- Mathematical Functions
- Table-Valued Functions
- Functions vs Stored Procedures
- Performance considerations
- Best practices
Mastering SQL Functions is essential because they are heavily used in enterprise applications, reporting systems, financial software, analytics platforms, and modern database development.
Next Step
Continue to the next lesson:
Triggers in SQL ā
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Functions in SQL.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, advanced, function
Related SQL Topics