Sql Functions
Different Types of SQL Server Functions
What is Function in SQL Server?
Function in SQL Server are database objects that include a group of SQL statements to
perform a specified activity. A function takes parameters, performs actions, and returns
the outcome. It should be noted that functions always return either a single value or a
table.
Rules for creating SQL Server Functions
The rules for writing SQL Server functions are as follows:
A function must be given a name, which cannot begin with a special character such as
@, $, #, or other similar characters.
The only statements that work with functions are SELECT statements.
AVG, COUNT, SUM, MIN, DATE, and other functions can be used anywhere with the
SELECT query in SQL.
When a function is invoked, it compiles.
Functions are required to return a value or result.
Only input parameters are used by functions.
TRY & CATCH statements are not permitted in functions.
Types of Function
SQL Server divides functions into two types:
System Functions
User-Defined Functions
1. System Defined Function
These functions are defined by SQL Server for different purposes. The functions that
are defined by the system are known as "system-defined functions". In other words, all
the built-in functions supported by the SQL server are referred to as system-defined
Prepared by: Zack daahir
Sql Functions
functions. Usage of the built-in functions saves much development time while
performing certain tasks. These types of functions generally work with the SQL select
statement to calculate the values and the manipulated data. We have two types of
system-defined functions in the SQL Server
1. Scalar Function
Scalar functions operate on a single value and return a single value. Below is a list of
some useful Scalar Functions in SQL Server.
Prepared by: Zack daahir
Sql Functions
2. Aggregate Function
Aggregate functions operate on a collection of values and return a single value. Below is
a list of some useful Aggregate Functions in SQL Server.
2. User-Defined Function
User-Defined Functions (UDFs) are user-created functions that encapsulate specialized
logic for use within SQL Server. They accept input, perform operations, and return
results, hence expanding database capabilities beyond built-in functions. These
functions are created by the user in the system database or a user-defined database.
We have three types of user-defined functions.
Scalar Function
The user-defined scalar function also returns a single value due to actions performed by
the function. We return any datatype value from a function.
Prepared by: Zack daahir
Sql Functions
Prepared by: Zack daahir
Sql Functions
Prepared by: Zack daahir
Sql Functions
Inline Table-Valued Function
The user-defined inline table-valued function returns a table variable due to actions
performed by the function. The value of the table variable should be derived from a
single SELECT statement.
Multi-Statement Table-Valued Function
A user-defined multi-statement table-valued function returns a table variable due to
actions performed by the function. In this, a table variable must be explicitly declared
and defined whose value can be derived from multiple SQL statements.
Prepared by: Zack daahir
Sql Functions
Prepared by: Zack daahir
Sql Functions
Check for understanding the lesson
Q1. Which three categories of SQL Server functions are there?
In SQL Server, user-defined functions come in three different varieties:
Scalar: Functions That Return A Single Value.
Inline Table Valued Functions: These functions return a Table Set with just one
TSQL statement.
Multiple Statements Table Valued Functions: (Returns Table Set; contains many
TSQL statements).
Q2. What is the purpose of any SQL Server function?
When a value from the first table matches every value from the second table, SQL ANY
returns the row with the matching value.
Q3. How many SQL functions are there?
Aggregate functions and scalar functions are the two categories under which SQL
functions fall.
Q4. How can I locate SQL functions?
You can list functions in the current database or in a different database by using the
SHOW FUNCTIONS command. The names of each function and the identity of the
function definer are returned by the function list.
Prepared by: Zack daahir
