SQL Server Functions
Functions in SQL Server are the database objects that contains a set of SQL statements to perform a specific task. A function accepts input parameters, perform actions, and then return the result. We should note that functions always return either a single value or a table. The main purpose of functions is to replicate the common task easily. We can build functions one time and can use them in multiple locations based on our needs. SQL Server does not allow to use of the functions for inserting, deleting, or updating records in the database tables.
The following are the rules for creating SQL Server functions:
Types of Functions
SQL Server categorizes the functions into two types:
Let us describe both types in detail.
Functions that are defined by the system are known as system functions. In other words, all the built-in functions supported by the server are referred to as System functions. The built-in functions save us time while performing the specific task. These types of functions usually work with the SQL SELECT statement to calculate values and manipulate data.
Here is the list of some system functions used in the SQL Server:
The following picture shows all the built-in database functions used in the SQL Server:
Functions that are created by the user in the system database or a user-defined database are known as user-defined functions. The UDF functions accept parameters, perform actions, and returns the result. These functions help us to simplify our development by encapsulating complex business logic and making it available for reuse anywhere based on the needs. The user-defined functions make the code needed to query data a lot easier to write. They also improve query readability and functionality, as well as allow other users to replicate the same procedures.
SQL Server categorizes the user-defined functions mainly into two types:
Here are the descriptions of these UDF functions.
Scalar function in SQL Server always accepts parameters, either single or multiple and returns a single value. The scalar functions are useful in the simplification of our code. Suppose we might have a complex computation that appears in a number of queries. In such a case, we can build a scalar function that encapsulates the formula and uses it in each query instead of in each query.
The following are the syntax illustrate the creation of scalar function in SQL Server:
The above syntax parameters are described below:
We will first define the function name after the CREATE FUNCTION keywords. The name of the schema is optional. If we will not define the schema name, SQL Server uses default schema dbo. Next, we will define the list of parameters enclosed in parenthesis. Third, we will write the statements for the function and then, in the RETURNS statement, define the data type of the return value. Finally, we have added the RETURN statement to return a value inside the function's body.
This example will create a function to calculate the net sales based on the quantity, price, and discount value:
Now, we can use this function to calculate the net sales of any sales order in the defined table.
The following picture display where we can find the scalar functions:
We can call the scalar functions the same as the built-in function in SQL Server. For example, we can call the above udfNet_Sales function as below:
Executing this function will return the net sales:
SQL Server also allows us to modify the scalar function by using the ALTER keyword. Here is the syntax to do this:
We can use the below statement to remove the scalar function from the SQL Server database:
Table-valued functions in SQL Server are the user-defined function that returns data of a table type. Since this function's return type is a table, we can use it the same way as we use a table.
We can categorize the table-valued function into two types:
1. Inline Table-Values Functions
This UDF function returns a table variable based on the action performed by the function. A single SELECT statement should be used to determine the value of the table variable.
The below example will create a table-values function and retrieve the data of the employee table:
In this syntax, the RETURNS TABLE specifies that the function will return a table. Since there is no BEGIN...END statement, it simply queries data from the employee table. Also, if it does not have any parameters, it will be called directly.
We can call the table-valued functions by using the FROM clause of the SELECT query. For example, we can call the above udf_GetEmployee function as below:
Executing this function will return the below result:
SQL Server also allows us to modify the table-valued functions using the ALTER keyword instead of the CREATE keyword. The rest of the script is the same.
Multi-statement table-valued functions (MSTVF)
This UDF function returns a table variable based on the action performed by the function. It can contain single or multiple statements to produce the result, and it is also a function that returns the result of multiple statements in a tabular form. It is useful because we can execute multiple statements in this function and get aggregated results into the returned table. We can define this function by using a table variable as the return value. Inside the function, we execute multiple queries and insert data into this table variable.
The following example creates a function name 'MULTIVALUED' that returns the '@Employee' table. It contains three fields named id, emp_name, and salary from the 'Employee' table using INSERT statement, and then uses UPDATE statement to update the employee name.
We can call the multi-statement table-valued functions by using the FROM clause of the SELECT query. For example, we can call the above function as below:
Executing this function will return the below result:
When should table-valued functions be used?
Table-valued functions are commonly used as parameterized views. Table-valued functions are more flexible than stored procedures because they can be used anywhere tables are used.
This article will explain a complete overview of functions used in the SQL Server. Here we have learned mainly two types of functions used in the SQL Server: system and user-defined functions.