SQL Server Functions- An Introduction

SQL FUNCTION: –
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)
Values(‘A001′,’Samir’,’Singh’,’samir@abc.com’,2)
Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)
Values(‘A002′,’Amit’,’Kumar’,’amit@abc.com’,1)
Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)
Values(‘A003′,’Neha’,’Sharma’,’neha@abc.com’,1)
Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)
Values(‘A004′,’Vivek’,’Kumar’,’vivek@abc.com’,1)
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)
Values(‘A007′,’Rajiv’,’Kumar’,’rajiv@abc.com’,5)
Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)
Values(‘A008′,’Manish’,’Kumar’,’manish@abc.com’,6)
SQL scripts for entering the data into the table Department:-
Insert Into Department(DepartmentName)
Values(‘Testing’)
Insert Into Department(DepartmentName)
Values(‘Admin’)
Insert Into Department(DepartmentName)
Values(‘HR’)
Insert Into Department(DepartmentName)
Values(‘Technology’)
SQL scripts for entering the data into the table EmpdepartmentInfo:-
Insert Into [EmpdepartmentInfo](empid, departmentid)
Values(1,1)
Insert Into [EmpdepartmentInfo](empid, departmentid)
Values(2,2)
Insert Into [EmpdepartmentInfo](empid, departmentid)
Values(3,3)
Insert Into [EmpdepartmentInfo](empid, departmentid)
Values(4,4)
Insert Into [EmpdepartmentInfo](empid, departmentid)
 Values(4,5)
Insert Into [EmpdepartmentInfo](empid, departmentid)
Values(5,1)
Insert Into [EmpdepartmentInfo](empid, departmentid)
Values(6,2)
Insert Into [EmpdepartmentInfo](empid, departmentid)
Values(7,3)
Insert Into [EmpdepartmentInfo](empid, departmentid)
Values(8,4)

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.

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

DMCA.com

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in Most Imp SQL Concepts, SQL Functions, SQL Server and tagged , , . Bookmark the permalink.

31 Responses to SQL Server Functions- An Introduction

  1. Pingback: doppelstegplatten

  2. Pingback: agencia de viajes en managua

  3. Pingback: Rome tour

  4. Pingback: pergola rouen

  5. Pingback: Small Dog Clothes

  6. Pingback: pita pit coupon 2019

  7. Pingback: 카지노사이트

  8. Pingback: วีไลน์

  9. Pingback: รูเล็ตออนไลน์

  10. Pingback: w88

  11. Pingback: check my blog

  12. Pingback: https://solidarnoscnse.pl/

  13. Pingback: UWAGA nie płaci za SEO

  14. Pingback: engagement rings

  15. Pingback: Dream Market Exit Scam

  16. Pingback: w88club

  17. Pingback: sen bir oğlansın

  18. Pingback: เซ็กซี่ บาคาร่า

  19. Pingback: small Dildo dp

  20. Pingback: fun 88

  21. Pingback: CBD oil

  22. Pingback: El día que mataron a Monseñor Romero

  23. Pingback: cbd oil tastes like

  24. Pingback: Dream Market

  25. Pingback: Cryptonia Market

  26. Pingback: trading

  27. Pingback: www.nuguru.info

  28. Pingback: senangpoker

  29. Pingback: lapak qq

  30. Pingback: Top 10 Dentist in Columbia Maryland

  31. Pingback: iPhone XR Screen Replacement for LCD Digitizer Amazon Best Seller

Leave a Reply