SQL Server Functions- An Introduction

By | August 2, 2011
   Function in a database can be defined as the code segment consisting of a logical group of SQL statements which takes some input arguments (if required), run in a sequential order and returns the output either in the form of a single value or in the form of a table.Through this article, I am trying to give the overview of the System defined functions,User defined functions, their advantages, their disadvantages and their differences with the Stored procedures. 

Types of Functions: –

In SQL, Functions can be categorized into two categories:-

  1. System Defined Function
  2. User Defined Function (UDF)
In this article, I am going to use the following tables for the examples:-
CREATE TABLE [dbo]. [Employee](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLAStName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Managerid] [int] NULL
CREATE TABLE [dbo].[Department](
[Departmentid] [int] IDENTITY (1, 1) NOT NULL primary key,
[DepartmentName] [nvarchar](255) NOT NULL
CREATE TABLE [dbo].[EmpdepartmentInfo]
 Empdepartmentid int identity(1,1) primary key,
 Empid int not null,
 departmentid int not null
SQL scripts for entering the data into the table Employee:-
Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)
Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)
Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)
Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)
Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)
Values(‘A005′,’ AvinASh’, ‘Dubey’,’avinASh@abc.com’,2)
Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)
Values(‘A006′,’ Sanjay’,’Kumar’,’ sanjay@abc.com’,5)
Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)
Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)
SQL scripts for entering the data into the table Department:-
Insert Into Department(DepartmentName)
Insert Into Department(DepartmentName)
Insert Into Department(DepartmentName)
Insert Into Department(DepartmentName)
SQL scripts for entering the data into the table EmpdepartmentInfo:-
Insert Into [EmpdepartmentInfo](empid, departmentid)
Insert Into [EmpdepartmentInfo](empid, departmentid)
Insert Into [EmpdepartmentInfo](empid, departmentid)
Insert Into [EmpdepartmentInfo](empid, departmentid)
Insert Into [EmpdepartmentInfo](empid, departmentid)
Insert Into [EmpdepartmentInfo](empid, departmentid)
Insert Into [EmpdepartmentInfo](empid, departmentid)
Insert Into [EmpdepartmentInfo](empid, departmentid)
Insert Into [EmpdepartmentInfo](empid, departmentid)

System defined function can again be further divided into further subsections which are given below:-

1. Aggregate function.
 Example: – Avg (), Min (), Max ()

2. Configuration function.
 Example:-@@servername (), @@version()

3. Cursor function.
Example: -@@Fetch_status

4. Date and Time function.
Example: – Getdate (), Month (), Day (), Year ()

5. Mathematical function.
 Example: – Floor (), Abs ()

6. Metadata function.
 Example: – Db_Name (), File_Name ()

7. Other functions.
 Example: – cast (), convert ()

8. Rowset function.
Example: – Openxml (), Openrowset ()

9. Security function.
Example:-user(), User_Id (), User_Name ()

10. String function.
Example: – Char (), Left (), Len ()

11. System Statistical function.

12. Text and Image function
Example: – Textvalid ()

Types of UDF:-

Similarly, UDF can be divided Into 3 categories:-

  1. Scalar UDF
  2. Inline Table UDF
  3. Multi statements UDF

Scalar UDF:-
The UDFs which only returns only single values comes into this category.

Syntax for creating Scalar UDFs:-

 (Input Variable name) (data type)
Returns (returning variable data type)
 (FUNCTION body)
 Return (returning variable name)


CREATE FUNCTION fn_getempname
 @empid Int
RETURNS nvarchar(100)
DECLARE @empname AS Nvarchar(100)

SELECT @empname=empfirstname + ‘ ‘ + emplAStname
FROM  employee
WHERE empid=@empid

RETURN @empname

Syntax for executing the Scalar UDF

SELECT dbo. (FUNCTION name) (input variable name)

For example, to execute the above UDF we use the following syntax:-

SELECT DBO.[fn_getempname](1) /* Here 1 is the empid*/


SELECT DBO.[fn_getempname](1) AS EmployeeName/*To get the output under the column EmployeeName */

If we want to store the value of this FUNCTION in an variable the we have to use the following syntax:-

DECLARE @name AS nvarchar(100)
SELECT @name =dbo.[fn_getempname](1)
SELECT @name

Here we first needs to define a variable (@name) which will be going to store the value return by the function and then used this variable in the SELECT statement to store the value.

Samir Singh


This function will return the name of the employee whose empid we passed in the function as the Input parameter.

Inline Table UDF:-

The UDF which contains a single inline SQL statement and returns the data in the form of table is called Inline Table UDF.

Syntax for creating Inline Table UDF:-

 (Input Variable name) (data type)
Returns Table
Return (SELECT statement)


Create Function fn_getempinfo
 @empid Int
Returns Table
 Return Select empid,empnumber,empfirstname,emplastname,empemail 
           From employee 
           Where empid=@empid

Syntax for executing the Inline Table UDFs

Select (columns names) from dbo. (Function name) (Input Parameter)

SELECT empid,empnumber,empfirstname,emplastname,empemail
FROM dbo.[fn_getempinfo](1)


Empid  Empnumber  Empfirstname Emplastname   Empemail

1         A001               Samir              Singh               samir@abc.com


This FUNCTION will return the columns empid, empnumber, empfirstname, emplAStname, empemail of the employee AS a table variable whose employeeid is given AS the input parameter to the FUNCTION.

Multi statements UDF: –

The UDFs which contain multiple SQL statements to returns the data in the form of table is called Multi Statements UDFs.

Syntax for creating Multi Statements UDFs: –

Create Function
 (Input Variable name) (data type)
Returns (table variable) Table (table columns)
 (Function body)


CREATE FUNCTION fn_GetEmpdepartmentinfo
 @empid Int
 Returns @Empproject Table
 Employeename Nvarchar(100),Empemail Nvarchar(50),Departmentname Nvarchar(100)

 Insert Into @Empproject(Employeename,Empemail,Departmentname)
 SELECT empfirstname + ‘ ‘+ emplAStname ,empemail,departmentname 
  FROM employee Inner Join EmpdepartmentInfo On employee.empid=EmpdepartmentInfo.empid 
  Inner join Department On EmpdepartmentInfo.departmentid=Department.departmentid
 WHERE employee.empid=@empid


Syntax for executing the Multi Statements UDF

Select (columns names) from dbo. (Function name) (Input Parameter)

SELECT Employeename,Empemail,Departmentname 
FROM dbo.[fn_GetEmpdepartmentinfo](1)


Employeename     Empemail              Departmentname 
Samir Singh          samir@abc.com        Accounts


This function will returns the Employeename,Empemail,Departmentname of the employee whose empid we pass as the input parameter to the function.

Difference between UDFs and Stored Procedures:-
  1. A stored procedure can return a value or it may not return any value but in case of function, a function has to return a value.
  2. Stored procedure in SQL Server cannot we executed within the DML statement. It has to be executed with the help of EXEC or EXECUTE keyword but a function can be executed within the DML statement.
  3. A function can be called from within the Stored Procedure but a stored procedure cannot be called from within a function.
  4. We can use result set return by the function as a table in Join statements but we can’t use ResultSet return from stored procedure as table in Join statements.
  5. Transaction management is not possible in function but it is possible in Stored procedures.
  6. Print function cannot be called within the function but it can be called within the stored procedure.

Advantages of UDF:-

1. SQL Functions can be used in a DML statement. It means we can use execute the FUNCTION within the SELECT statement.

2. We can use the recursive FUNCTION to get the hierarchical information.

For example, if we want to get the all the employee which are directly or indirectly have the manager whose empid is given AS the input parameter(@managerid), then we can use the following FUNCTION which calls itself.

CREATE FUNCTION fn_recuursivesample
 @managerid int,
 @mode int
Returns @temporder table(employeeid int , managerid int)


 DECLARE @count AS int
 DECLARE @empid AS int
 DECLARE @next_empid AS int
 DECLARE @next_orderid AS int

 IF @mode=0
    INSERT Into @temporder
    SELECT @managerid,(SELECT managerid FROM employee WHERE empid=@managerid)

SELECT @count=count(empid)FROM employee WHERE managerid=@managerid
 IF @count=1
  SELECT @empid=empid FROM employee WHERE managerid=@managerid
  INSERT Into @temporder Values(@empid,@managerid)
  INSERT Into @temporder SELECT * FROM dbo.fn_recuursivesample(@empid,1)
ELSE IF @count>1
   SELECT @empid=min(empid)FROM employee WHERE managerid=@managerid
   WHILE @empid>0
           Insert Into @temporder Values(@empid,@managerid)

           Insert Into @temporder 
           SELECT * FROM dbo.fn_recuursivesample(@empid,1)

           SELECT @next_empid=isnull(min(empid),0)
           FROM employee
           WHERE empid >@empid and managerid=@managerid
        SET @empid=@next_empid

Syntax to execute the above function

SELECT employeeid, managerid FROM dbo. fn_recuursivesample(1,0)


Employeeid        Managerid
     1                            0
     2                            1
     5                            2
     6                            5
     8                            6
     7                            5
     3                            1
     4                            4
3. We can use the Function in the Join queries.

4. We can used UDFs as the parametrized view(a view which take input parameters).

5. UDFs also reduce the compilation cost of SQL codes by caching the plans and reusing them for repeated executions which mean it does not need to be re-parsed and recompiled with each use and it result in better execution time.

6. We can use the WHERE clause to filter the rows as per our needs FROM the result set return by the Function. For example, in the above function fn_recuursivesample, if we want to get the empid of only those employee which are directly under the employee whose empid is given AS the input parameter, the we can use the “WHERE” clause to filter the dataset return by the function.

SELECT employeeid, managerid FROM dbo. fn_recuursivesample(1,0) WHERE managerid=1

It will give the following result:-

Employeeid   Managerid
2                       1
3                       1
4                       1

7. UDFs can be used to divide the complex code into shorter and simple blocks of code which helps in maintenance of the code.

Disadvantages of the UDF

  1. We cannot use temporary tables inside the UDF.
  2. We cannot use Print command inside the UDF.
  3. We can’t use the Insert, update and delete command inside the function. We can only use these commands on the table variable defined inside the function.
  4. We cannot use the try catch statement inside the function for the debugging purpose which makes it difficult to debug.
  5. We can’t use transaction inside the function.
  6. We can’t call the stored procedure from inside the function.
  7. We can’t return multiple record sets from a function as we can do in case of Stored Procedure.
  8. We can’t use Getdate() function  within a UDF.

    Functions are the feature which is given by the SQL Server to make our complex code shorter and less complex. We can either use the Functions which are already provided by the SQL Server known as the System defined Function such as Avg (), Min (), Max () or we can write our own UDF. If we want to get only a scalar value as result, we can create Scalar UDFs or if we want to get a ResultSet, then we can create Inline Table UDFs or Multi statements UDFs. We can also used Functions in the SELECT statement and in join statements. But while creating the UDFs we should also keeps in our mind that no DML(Insert, Update, Delete) statement can be performed inside the UDF. Also we can’t use temporary table inside the Function. Also we can’t use transaction management inside the UDF.


Leave a Reply