T-SQL Tutorial

Types of User-Defined Functions in SQL


SQL Server Functions

In SQL, server functions are the objects of the database in which there is a group of SQL statements for a particular task. Input parameters are accepted by the function and then action is performed and then finally the result is returned. Either a table or a single value is returned by the function. The main objective of the function is to do common task replication.


The function is built by us once and we can use that function according to our requirements at any location. Functions for deleting, inserting and updating are not allowed in the SQL server. Below are some rules are given that are to be followed while creating the SQL SERVER function:

  • There must be a name for the function and the name of the function never start with a special symbol such as @,#,$ and other characters.
  • Only the SELECT Statements is only that can be operated with the function.
  • AVG, SUM, COUNT, MIN, DATE, and other functions can be used anywhere in SQL Select statements.
  • Compilation takes place at every function call
  • Value or result must be returned by the function
  • Only input parameters can be used with the function.
  • Try and catch are not allowed to be used in the functions.

Types of Functions

There are two types of functions in the SQL server

  • System Functions
  • User-Defined Functions

System Functions

System Functions are those functions that the system defines. All the built-in functions that our server supports are known as System functions. When the particular task is performed by the built-in functions, then they consume less time. SQL SELECT is used by built-in functions in SQL to manipulate and calculate the record. SQL Partition by function is also a commonly used one.


Some system functions list is given below:

  • String functions such as REPLACE, SUBSTRING, LEN, TRIM, and CONCAT.
  • datetime, smalldatetime, and datetime2 are the type of date and time functions.
  • Aggregate functions such as SUM, MAX, MIN, COUNT, and AVG.
  • The list of mathematical functions is ABS, POWER, EXP, LOG, and PI.
  • Ranking functions are DENSE_RANK, ROW_NUMBER, RANK, and NTILE.

User-Defined Function

There are some functions in the system database or user-defined databases that are created by the user. These functions are known as user-defined functions. Parameters can be accepted by these functions for performing a particular task and after it returns desired output. Using user-defined functions makes the development of the whole database very simple as they encapsulate the complex business logic and also provide the facility of using it in future whenever there is a need for the same function. It provides the facility to improve the readability of the query, accessibility and functionality of the query and also allows the developers to replicate the same procedure according to their requirements.

There are three types of user-defined functions. The following are the type of user-defined functions:

  • Scalar Function
  • Inline Table-Valued Function
  • Multi-Statement Table-Valued Function

Example

Let us create a table with the name Student with the attributes id, first_name, last_name and address and add the data into it to demonstrate the types of user-defined functions on the table.

CREATE TABLE STUDENT
(
ID INT PRIMARY KEY,
FIRST_NAME VARCHAR(50) NULL,
LAST_NAME VARCHAR(50) NULL,
ADDRESS VARCHAR(100) NULL
)

Inserting data into the table

INSERT INTO STUDENT(ID,FIRST_NAME,LAST_NAME,ADDRESS)
VALUES(1,’RUCHI’,’SHARMA’,'DELHI');

INSERT INTO STUDENT(ID,FIRST_NAME,LAST_NAME,ADDRESS)
VALUES(2,’RICHA’,’PATEL’,'NOIDA');

INSERT INTO STUDENT(ID,FIRST_NAME,LAST_NAME,ADDRESS)
VALUES(3,’SAKSHI’,’GOYAL’,'HARYANA');

INSERT INTO STUDENT(ID,FIRST_NAME,LAST_NAME,ADDRESS)
VALUES(4,’RAJAT’,’VERMA’,'CHENNAI');


Scalar Function

A single value is returned as the result of the task performed by the user-defined scalar function.
The value of any datatype can be returned from these functions.

Example: Creating the scalar function to return FIRST_NAME and LAST_NAME as a single column.

Create function functionGetFullStudentName
(
@FIRST_NAME varchar(50),
@LAST_NAME varchar(50)
)
returns varchar(101)
As
Begin return (Select @FIRST_NAME + ' '+ @LAST_NAME);
end

Calling the above defined function on our example table

Select dbo.functionGetFullStudentName(FIRST_NAME,LAST_NAME) as Name, Address from Student;


Inline Table-Valued Function

These types of user-defined functions return a table variable in the form of the output of tasks performed by these functions. The single SELECT command is used to drive the value of the table variable.

Example: Creating the Inline Table-Valued Function to return Student table data

Create function functionGetAllStudents()
returns Table
As
return (Select * from STUDENT)

Calling the above defined function

Select * from functionGetAllStudents();


Multi-Statement Table-Valued Function

Multi-Statement Table-Valued Functions also return a table variable in the form of the output of the task performed by these functions. But in these types of functions, there is a need to explicitly define and declare the value of which data can be derived from multiple SQL statements.

Example: Creating the Multi-Statement Table-Valued Function to return updated data of the table by updating the address of the student with id 1 to the Ghaziabad

Create function functionGetModifiedDataOfStudents()
returns @Stu Table
(
ID int,
FIRST_NAME varchar(50),
ADDRESS varchar(50)
)
As
begin
Insert into @Stu Select s.ID,s.FIRST_NAME,s.ADDRESS from STUDENT s;
update @Stu set ADDRESS=’Ghaziabad’ where ID=1;
return
end

Calling above defined function

Select * from functionGetModifiedDataOfStudents();

This function displays the updated data of the student but the data in the original table is not updated


Note

  • In contrast to stored procedures, a single value is returned by the function.
  • In contrast to stored procedures, only input parameters are accepted by the function.
  • In contrast to stored procedures, the function is not utilized for the operation of insert, delete, and update on the database.
  • Similar to the stored procedures, nesting of functions can also be done till the 32 levels.
  • There can be 1023 input parameters in the user-defined function and 2100 input parameters are available in the stored procedure.
  • The XML data type is not returned by the user-defined functions.
  • Exception handling is not possible in user-defined functions.
  • Only Extended Stored procedures can be called in User Defined Function.
  • Set options such as Set ROWCOUNT, etc are not allowed in the user-defined functions.

Benefits of User-Defined Functions

Modular Programming: Modular Programming feature is provided by the user-defined functions as a function is created once and then created function stored in the database and then this function can be called any number of times. Modification of user-defined functions can be done independently of the source code of the program.

Faster execution: Just like stored procedures, compilation cost is reduced by the user-defined functions by plan caching and using them repeatedly for execution. So that there is no need for parsing and re-optimizing the user-defined functions at every use and this will result in much faster execution time. The performance advantage is offered by the CLR function on the user-defined function for string manipulation, computational tasks, and business logic.

Reduce network traffic: The single scalar expression used for expressing the operation used for data filtering based on complex constraints can also be represented by the function. And then to decrease the number of rows sent to the client, these functions can be used in the WHERE clause of the SQL statement.