Different Types of SQL Server Functions

Different Types of SQL Server Functions: An Overview

Explore various functions such as user-defined and system functions in this SQL Server tutorial to improve your database querying skills. A function is a database object in SQL Server. It is a set of SQL statements that accept only input parameters, perform actions, and return the result. For more about the stored procedure and function refer to the article Difference between Stored Procedure and Function in this SQL Course.

What is Function in SQL Server?

Function in SQL Server are database objects that include a group of SQL statements to perform a specified activity. A function takes parameters, performs actions, and returns the outcome. It should be noted that functions always return either a single value or a table.

Rules for creating SQL Server Functions

The rules for writing SQL Server functions are as follows:

• A function must be given a name, which cannot begin with a special character such as @, $, #, or other similar characters.
• The only statements that work with functions are SELECT statements.
• AVG, COUNT, SUM, MIN, DATE, and other functions can be used anywhere with the SELECT query in SQL.
• When a function is invoked, it compiles.
• Functions are required to return a value or result.
• Only input parameters are used by functions.
• TRY & CATCH statements are not permitted in functions.

Types of Function

SQL Server divides functions into two types:

1. System Functions
2. User-Defined Functions

1. System Defined Function

These functions are defined by SQL Server for 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 the SQL Server

1. Scalar Function

   Scalar functions operate on a single value and return a single value. Below is a list of some useful Scalar Functions in SQL Server.

   System Scalar Function

   Scalar Function

   Description

   abs(-10.67)

   This returns an absolute number of the given number, which 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.567

   upper('dotnet')

   This will return the upper case of the given string meaning 'DOTNET'

   lower('DOTNET')

   This will return the lowercase 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.

2. Aggregate Function

   Aggregate functions operate on a collection of values and return a single value. Below is a list of some useful Aggregate Functions in SQL Server.

   System Aggregate Function

   Aggregate Function

   Description

   max()

   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.

2. User-Defined Function

User-Defined Functions (UDFs) are user-created functions that encapsulate specialized logic for use within SQL Server. They accept input, perform operations, and return results, hence expanding database capabilities beyond built-in functions. These functions are created by the user in the system database or a user-defined database. We have three types of user-defined functions.

1. Scalar Function

   The user-defined scalar function also returns a single value due to 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 

   Creates the "Employee" table, which has columns for employee ID, first name, last name, salary, & address.Inserts data for four employees into the table, including their contact information. All data from the "Employee" database is retrieved and shown.

   

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

   The function "fnGetEmpFullName" is created, which accepts first and last names as input. Concatenates the first and last names, separated by a space, to make the whole name. The created full name is returned as a varchar(101) string.

   

   --Calling the above created function
   Select dbo.fnGetEmpFullName(FirstName,LastName) as Name, Salary from Employee 

   Full names are obtained by invoking the "fnGetEmpFullName" function and giving first and last names from the "Employee" table. The generated full names are renamed "Name" for clarity. Retrieves each employee's "Salary" column. Displays the combined results, including full names and salaries.

   

2. Inline Table-Valued Function

   The user-defined inline table-valued function returns a table variable due to 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) 

   This query creates a function called "fnGetEmployee" that retrieves all data (marked by the symbol "*") from the "Employee" table. Returns the obtained data as a table, making it available for use in subsequent queries or actions.

   

   --Now call the above created function
   Select * from fnGetEmployee() 

   The previously declared "fnGetEmployee" function is invoked, which retrieves all employee data. Treats the returned data as a table, allowing for direct selection of all columns with "*". Displays all employee information retrieved from the function's output.

   

3. Multi-Statement Table-Valued Function

   A user-defined multi-statement table-valued function returns a table variable due to 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 

   Creates a function called "fnGetMulEmployee" that returns a custom table variable called "@Emp" that has columns for employee ID, first name, and salary. Fills the "@Emp" table with data from the "Employee" table. Modify the salary of the first employee in the "@Emp" database, but without affecting the original "Employee" table, guaranteeing data integrity. As the function's output, it returns the changed "@Emp" table.

   

   --Now call the above created function
   Select * from fnGetMulEmployee() 

   This query uses the "fnGetMulEmployee" method to retrieve a custom table with employee IDs, first names, and salaries, with the first employee's pay modified to 25000.

   

   --Now see the original table. This is not affected by above function update command
   Select * from Employee 

   This query gets & displays the original "Employee" table, confirming that its data is unaltered because function modifications were limited to the temporary table.

   

Difference between Function and Procedure in SQL Server

Note

1. Unlike the Stored Procedure, the Function returns only a single value.

2. Unlike the Stored Procedure, the Function accepts only input parameters.

3. Unlike the Stored Procedure, the Function is not used to Insert, Update, or Delete data in a database table(s).

4. Like the Stored Procedure, the Function can be nested up to 32 levels.

5. User Defined Function can have up to 1023 input parameters while a Stored Procedure can have up to 2100 input parameters.

6. User Defined Function can't return XML Data Type.

7. User Defined Function doesn't support Exception handling.

8. User Defined Function can call only Extended Stored Procedure.

9. User Defined Function doesn't support set options like set ROWCOUNT etc.

Read More:

• Top SQL Interview Questions and Answers

Summary

SQL Server functions extend the capabilities of databases by encapsulating logic, accepting input, conducting actions, and returning results. They are classified as either system-defined or user-defined, with variations within each kind to meet unique requirements.

Take our free sqlserver skill challenge to evaluate your skill

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET CHALLENGE