1. System Defined Function
2. User Defined Function (UDF)

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.
 Example:-@@connections


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:-


CREATE FUNCTION (FUNCTION name)
(
 (Input Variable name) (data type)
)
Returns (returning variable data type)
AS
 BEGIN
 (FUNCTION body)
 Return (returning variable name)
 End


Example


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


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


RETURN @empname
END


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*/


or


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.


Result
EmployeeName
Samir Singh


Explanation


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:-


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


Example:-

Create Function fn_getempinfo
(
 @empid Int
)
Returns Table
As
 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)


Result


Empid  Empnumber  Empfirstname Emplastname   Empemail


1         A001               Samir              Singh               samir@abc.com


Explanation


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)
As
 Begin
 (Function body)
 Return
 End

Example


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


 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


RETURN
END


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)


Result


Employeename     Empemail              Departmentname 
Samir Singh          samir@abc.com        Accounts


Explanation


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

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)
AS


BEGIN


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


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


SELECT @count=count(empid)FROM employee WHERE managerid=@managerid
 IF @count=1
 BEGIN
  SELECT @empid=empid FROM employee WHERE managerid=@managerid
  INSERT Into @temporder Values(@empid,@managerid)
  INSERT Into @temporder SELECT * FROM dbo.fn_recuursivesample(@empid,1)
 END
ELSE IF @count>1
  BEGIN
   SELECT @empid=min(empid)FROM employee WHERE managerid=@managerid
   WHILE @empid>0
        BEGIN
           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
   END
END
RETURN
END


Syntax to execute the above function


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


Result:-


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.

Conclusions
    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.