Introduction to SQL Server Functions With Example

SQL Server have functions which can be defined simply as database object or set of SQL statements which accept only input parameters, perform actions like calculations, and return value as a result. This result can be a single value and is scalar. Functions cannot be used to Update, Delete or Insert records to the SQL databases.

SQL Functions and Types

Majorly, Functions can be categorized as;

1. System Defined Function
2. User-defined Function

System-defined Function

These kinds of functions are defined by the SQL Server itself to be utilized in different instructions for different purpose. There are two types of system defined function;

1. Scalar Function

These SQL Scalar functions return as a result a single value and operate on a single value. Let us see some examples;

System Scalar Functions

Scalar Function		Description
abs(-3.14)		Absolute number will be returned i.e. 3.14.
rand(6)		Allows generation of random number with 6 characters.
round(54.73871,4)		Round-off figure will be returned to 4 places of decimal 54.7387
upper('abcd')		Uppercase of provided string i.e. 'ABCD'
lower('ABCD')		Lower case of given string i.e. 'abcd'
ltrim(' abcd')		This will trim out the spaces from left hand side of 'abcd' string.
convert(int, 6.14)		Given float value will be converted to integer i.e. 6.

      2. Aggregate Function

Aggregate functions work on collection of values returning single value as result. Let us see some of the examples of Aggregate functions;

Aggregate Function			Description
max()			Maximum value from a collection of values will be returned.
min()			Minimum value from a collection of values will be returned.
avg()			Average value will be returned from the values inside collection.
count()			No of counts available inside a collection will be returned.

User Defined Function:

User-defined functions are created by user in user-defined database or system database. Let us see about these types and their examples;

1. Scalar Function

Single value will be returned as a result by user-defined scalar function also. Any type of data type value can be returned from the function.

2. Inline Table-Valued Function

Result of user-defined inline table-valued function is a table variable which is returned after actions performed by the function. The value of table variable should be derived from a single SELECT statement.

       3. Multi-statement Table-Valued Function

Multi-statement scalar function has a function body inside BEGIN and END block with multiple statements. It returns a table-variable as a result followed by the operation of program. It is important to explicitly declare a table variable whose values will be returned from multiple SQL statements.

User-defined Function Benefits:

• Help in modular programming: Function can be created and stored in the database. Once it is stored it can be called number of times in the SQL program. User-defined functions can be modified without depending upon the program source code.
• Faster execution is done by catching plans and using them again and again for repeated executions.
• Network traffic is also reduced as data is filtered with some operations.

Noteworthy Points:

• Function will return only single value, accepts only input parameters, and function is not used to Insert, Delete or Update in database.
• Function can be nested up to 32 levels and does not support Exception handling.
• Function cannot return XML data type, and can call only Extended Stored Procedure.

Conclusion:

In the section discussed above, we discussed about different types of function in SQL server along with their examples. Functions main category has been also elaborated.