Different Types of SQL Server Functions
A function is a database object in SQL Server. Basically, it is a set of SQL statements that accept only input parameters, perform actions and return the result. The function can return only a single value or a table. We can’t use a function to Insert, Update, Delete records in the database table(s). For more about stored procedure and function refer to the article Difference between Stored Procedure and Function
Types of Function
System Defined Function
These functions are defined by SQL Server for a 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 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 SQL Server
Scalar functions operate on a single value and return a single value. Below is the list of some useful SQL Server Scalar functions.Scalar FunctionDescriptionabs(-10.67)This returns an absolute number of the given number means 10.67.rand(10)This will generate a random number of 10 characters.round(17.56719,3)This will round off the given number to 3 places of decimal meaning 17.567upper('dotnet')This will return the upper case of the given string meaning 'DOTNET'lower('DOTNET')This will returns the lower case of the given string means 'dotnet'ltrim(' dotnet')This will remove the spaces from the left-hand side of the 'dotnet' string.convert(int, 15.56)This will convert the given float value to integer means 15.
Aggregate functions operate on a collection of values and return a single value. Below is the list of some useful SQL Server Aggregate functions.Aggregate FunctionDescriptionmax()This returns the maximum value from a collection of values.min()This returns the minimum value from a collection of values.avg()This returns an average of all values in a collection.count()This returns no of counts from a collection of values.
Functions that are created by the user or developer in the system database or a user-defined database are generally known as "user-defined functions". The user-defined functions may accept required parameters, perform certain actions, and return the processed data. These custom functions help us to simplify the overall database development by encapsulating the complex business logic and making it available for reuse whenever any similar functionality is required. The user-defined functions hold the code that is needed to query data a lot easier to write. It also improves query readability, accessibility, and functionality, as well as allows other developers to replicate the same procedures accordingly.
These functions are created by the user in the system database or in a user-defined database. We have three types of user-defined functions.
The user-defined scalar function also returns a single value as a result of actions performed by the function. We return any datatype value from a function.
--Create a table CREATE TABLE Employee ( EmpID int PRIMARY KEY, FirstName varchar(50) NULL, LastName varchar(50) NULL, Salary int NULL, Address varchar(100) NULL, ) --Insert Data Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(1,'Mohan','Chauahn',22000,'Delhi'); Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(2,'Asif','Khan',15000,'Delhi'); Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(3,'Bhuvnesh','Shakya',19000,'Noida'); Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(4,'Deepak','Kumar',19000,'Noida'); --See created table Select * from Employee
--Create function to get emp full name Create function fnGetEmpFullName ( @FirstName varchar(50), @LastName varchar(50) ) returns varchar(101) As Begin return (Select @FirstName + ' '+ @LastName); end
--Calling the above created function Select dbo.fnGetEmpFullName(FirstName,LastName) as Name, Salary from Employee
Inline Table-Valued Function
The user-defined inline table-valued function returns a table variable as a result of actions performed by the function. The value of the table variable should be derived from a single SELECT statement.
--Create function to get employees Create function fnGetEmployee() returns Table As return (Select * from Employee)
--Now call the above created function Select * from fnGetEmployee()
Multi-Statement Table-Valued Function
A user-defined multi-statement table-valued function returns a table variable as a result of 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.
--Create function for EmpID,FirstName and Salary of Employee Create function fnGetMulEmployee() returns @Emp Table ( EmpID int, FirstName varchar(50), Salary int ) As begin Insert into @Emp Select e.EmpID,e.FirstName,e.Salary from Employee e; --Now update salary of first employee update @Emp set Salary=25000 where EmpID=1; --It will update only in @Emp table not in Original Employee table return end
--Now call the above created function Select * from fnGetMulEmployee()
--Now see the original table. This is not affected by above function update command Select * from Employee
Unlike Stored Procedure, Function returns an only a single value.
Unlike Stored Procedure, Function accepts only input parameters.
Unlike Stored Procedure, Function is not used to Insert, Update, Delete data in a database table(s).
Like Stored Procedure, Function can be nested up to 32 levels.
User Defined Function can have up to 1023 input parameters while a Stored Procedure can have up to 2100 input parameters.
User Defined Function can't return XML Data Type.
User Defined Function doesn't support Exception handling.
User Defined Function can call only Extended Stored Procedure.
User Defined Function doesn't support set options like set ROWCOUNT etc.
A function in SQL Server is a set of SQL is a series of statements that perform specific intended tasks. Functions are the best way to achieve higher code reusability and If we have to repeatedly write the intense SQL scripts to perform the same task then we can create a function that performs that similar task seamlessly. So by the next time onwards, instead of rewriting the SQL, you can simply call the user-defined functions. A function may accept some inputs in the form of parameters or arguments and returns some processed values. SQL Server comes with a variety of in-built functions that perform a variety of tasks accordingly.
In this article, I try to explain the types of functions in SQL servers with an example. I hope after reading this article you will be aware of the function of SQL Server. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.
Take our free skill tests to evaluate your skill!
In less than 5 minutes, with our skill test, you can identify your knowledge gaps and strengths.