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:

A function must have a name, and the name cannot begin with a special character such as @, $, #, or other similar characters.
SELECT statements are the only ones that operate with functions.
We can use a function anywhere such as AVG, COUNT, SUM, MIN, DATE, and other functions with the SELECT query in SQL.
Whenever a function is called, it compiles.
Functions must return a value or result.
Functions use only input parameters.
We cannot use TRY and CATCH statements in functions.

Types of Functions

SQL Server categorizes the functions into two types:

System Functions
User-Defined Functions

Let us describe both types in detail.

System Functions

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:

String Functions (LEN, SUBSTRING, REPLACE, CONCAT, TRIM)
Date and Time Functions (datetime, datetime2, smalldatetime)
Aggregate Functions (COUNT, MAX, MIN, SUM, AVG)
Mathematical Functions (ABS, POWER, PI, EXP, LOG)
Ranking Functions (RANK, DENSE_RANK, ROW_NUMBER, NTILE)

The following picture shows all the built-in database functions used in the SQL Server:

User-Defined Functions

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:

Scalar Functions
Table-Valued Functions

Here are the descriptions of these UDF functions.

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

CREATE FUNCTION schema_name.function_name (parameter_list)
RETURNS data_type AS
BEGIN
    statements
    RETURN value
END 

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.

Example

This example will create a function to calculate the net sales based on the quantity, price, and discount value:

CREATE FUNCTION udfNet_Sales(
    @quantity INT,
    @price DEC(10,2),
    @discount DEC(3,2)
)
RETURNS DEC(10,2)
AS 
BEGIN
    RETURN @quantity * @price * (1 - @discount);
END; 

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:

ALTER FUNCTION schema_name.function_name (parameter_list)
RETURNS data_type AS
BEGIN
    statements
    RETURN value
END

We can use the below statement to remove the scalar function from the SQL Server database:

Table-Valued Functions

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.

Example

The below example will create a table-values function and retrieve the data of the employee table:

--It creates a table-valued function to get employees
CREATE FUNCTION fudf_GetEmployee()
RETURNS TABLE
AS
 RETURN (SELECT * FROM Employee)

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.

CREATE FUNCTION MULTIVALUED()
RETURNS @Employee TABLE
(id INT, emp_name VARCHAR(50), salary INT) AS
BEGIN
    INSERT INTO @Employee
    SELECT E.id, E.emp_name, E.salary FROM Employee E;
	UPDATE @Employee SET emp_name = 'Graeme Smith' WHERE id = 3;
    RETURN
END 

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.

Conclusion

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.

Next TopicSQL Server Window Functions

← prev next →