SQL – Functions

In SQL Server, Functions are stored programs which allow you to encapsulate reusable logic. In a very simple manner, we can understand them as the set of SQL statements that accepts only input parameters, perform actions and return the result which follows the computer-science definition.

You can instantiate and modify any number of local variables with in a function however you cannot modify anything outside of the scope of the function. As we know that functions normally have an output and optionally inputs and these outputs can be used as the input to another function (a SQL Server built-in such as DATEDIFF, LEN, etc.) or as a predicate to a SQL Query.

Interesting facts, SQL Server functions, like cursors, are meant to be used as our last weapon! They do have performance issues and therefore using a table-valued function should be avoided as much as possible. 

Types of Function

Based on the different purposes and requirements, SQL Server provides the two major categories of the functions such as -

1. System Defined Function - These functions are defined by SQL Server for performing calculations on data.  Some of them are given below which are the most common SDFs.

• SQL Aggregate Functions: SQL aggregate functions return a single value, calculated from values in a column. Most common and useful aggregate functions:

System Aggregate Function
Aggregate Function	Description
max()	This returns maximum value from a collection of values.
min()	This returns minimum value from a collection of values.
avg()	This returns average of all values in a collection.
SUM ()	This returns sum of all values in a collection.
count()	This returns no of counts from a collection of values.

•  SQL Scalar functions: SQL scalar functions return a single value, based on the input value. Most common and useful scalar functions:

System Scalar Function
Aggregate Function	Description
UCASE()	Converts a field to upper case
LCASE()	Converts a field to lower case
MID()	Extract characters from a text field
LEN()	Returns the length of a text field
ROUND()	Rounds a numeric field to the number of decimals specified
NOW()	Returns the current system date and time
FORMAT()	Formats how a field is to be displayed

2. User Defined Function: These functions are created by user in system database or in user defined database. User-defined functions cannot be used to perform actions that modify the database state. User-defined functions, like system functions, can be invoked from a query. Scalar functions can be executed by using an EXECUTE statement like stored procedures.

SQL Server supports scalar-valued UDFs and table-valued UDFs. Functions are scalar-valued if the RETURNS clause specified one of the scalar data types. Scalar-valued functions can be defined by using multiple Transact-SQL statements.

Scalar-valued functions can be invoked where scalar expressions are used. This includes computed columns and CHECK constraint definitions. Scalar-valued functions can also be executed by using the EXECUTE statement. Scalar-valued functions must be invoked by using at least the two-part name of the function.

Functions are table-valued if the RETURNS clause specified TABLE. Depending on how the body of the function is defined, table-valued functions can be classified as inline or multi-statement functions.

Note: User-defined functions that return a table data type can be powerful alternatives to views. These functions are referred to as table-valued functions. A table-valued user-defined function can be used where table or view expressions are allowed in Transact-SQL queries. While views are limited to a single SELECT statement, user-defined functions can contain additional statements that allow more powerful logic than is possible in views. A table-valued user-defined function can also replace stored procedures that return a single result set. The table returned by a user-defined function can be referenced in the FROM clause of a Transact-SQL statement, but stored procedures that return result sets cannot. In a table-valued user-defined function: The RETURNS clause defines a local return variable name for the table returned by the function. The RETURNS clause also defines the format of the table. The scope of the local return variable name is local within the function. The Transact-SQL statements in the function body build and insert rows into the return variable defined by the RETURNS clause. When a RETURN statement is executed, the rows inserted into the variable are returned as the tabular output of the function. The RETURN statement cannot have an argument. No Transact-SQL statements in a table-valued function can return a result set directly to a user. The only information the function can return to the user is the table returned by the function.

Allowed Statements in SQL Functions: The following statements are valid in a function:

1. Control-of-Flow statements except TRY...CATCH statements. 
2. Assignment statements.
3. DECLARE statements defining local data variables and local cursors.
4. SELECT statements that contain select lists with expressions that assign values to local variables.
5. Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.
6. INSERT, UPDATE, and DELETE statements modifying local table variables.
7. EXECUTE statements calling extended stored procedures.

Disallowed SQL Statements: The following Service Broker statements cannot be included in the definition of a Transact-SQL user-defined function:

1. BEGIN DIALOG CONVERSATION
2. END CONVERSATION
3. GET CONVERSATION GROUP
4. MOVE CONVERSATION
5. RECEIVE
6. SEND

Some Basic Rules for Functions: There are some basic few rules that must be followed when creating a function:

1. The body of the function must be enclosed in a BEGIN/END block.
2. Statements with side effects (insert/update/delete) and temporary tables may not be used. You can, however, use table variables. Table variables are allowed in UDFs because they are created as variables, not through DDL. DDL is viewed as producing a side effect and is not allowed.
3. TRY/CATCH statements are not allowed since CATCH can have the side effect of masking the error state of a given function.

To understand the functionality of SQL function, we will create an Employee table in our database such as given below:

--Create Employee table CREATE TABLE Employee (  EmpID int PRIMARY KEY,  FirstName varchar(50) NULL,  LastName varchar(50) NULL,  Salary int NULL,  Address varchar(100) NULL, ) --Insert Values into the employee table Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(1,'Ryan','Arjun',22000,'Delhi, India'), (2,'Jullie','Philips',15000,'New York, USA'), (3,'Black','Smith',19000,'Sydney, Australia'), (4,'Rosey','White',19000,'London, England'); --Fetch records from the employee table Select * from dbo.Employee EmpID FirstName LastName Salary Address 1 Ryan Arjun 22000 Delhi, India 2 Jullie Philips 15000 New York, USA 3 Black Smith 19000 Sydney, Australia 4 Rosey White 19000 London, England

Now, we have the employee table and need to create all types of User Defined Functions which are based on this table.

Scalar-Valued UDFs- User defined scalar function also returns single value as a result of actions perform by function. The return type can be any data type except text, ntext, image, cursor, spatial, hierarchyID, and timestamp.

-- Create function to get emp full name -- With Two Parameters such as First Name and Last Name -- Which will be returned as the combination of the both parameters Create function funEmployeeFullName (  @FirstName varchar(50),  @LastName varchar(50) ) returns varchar(101) As Begin -- Return the final value return (Select @FirstName + ' '+ @LastName); end -- Select Value with function also Select EmpId, -- Calling Scalar Valued Function dbo.funEmployeeFullName(FirstName,LastName) as FullName, Salary from dbo.Employee EmpId FullName Salary 1 Ryan Arjun 22000 2 Jullie Philips 15000 3 Black Smith 19000 4 Rosey White 19000

Scalar UDFs are a fairly straightforward feature but there are some drawbacks to them, the biggest one being that SQL Server has no optimization whereby it can compile this function as inline code. 

Therefore, it will simply call it once for every row to be returned in the result set. Another drawback of scalar UDFs is that we won't see the true cost of the function when we're looking at execution plans. This makes it difficult to gauge just how much a UDF is hurting query performance.

Table-valued Functions

Table-valued Functions differ from scalar functions in that TVFs return an entire table whereas scalar functions only return a single value.

Inline Table-Value user-defined function: An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.

--Create Inline Table Valued function to get employees Create function fnGetAllEmployee() -- return type should be a table returns Table As return (Select * from Employee); -- Call the function to get value Select * from dbo.fnGetAllEmployee() EmpID FirstName LastName Salary Address 1 Ryan Arjun 22000 Delhi, India 2 Jullie Philips 15000 New York, USA 3 Black Smith 19000 Sydney, Australia 4 Rosey White 19000 London, England

Multi-statement Table-Value User-Defined Function: A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Within the create function command we must need to define the table structure that is being returned as the output of the function.

As we know that a multi-statement TVF is one that contains more than one statement in the function body based on the requirements.

-- Create Table Valued Function Create function fnMultiEmployee(@Salary Int) -- declare table variable with -- body structure returns @Emp Table ( EmpID int, FirstName varchar(50), Salary int ) As Begin -- Statement 1: -- Insert values into table variable Insert into @Emp (EmpID, FirstName, Salary) -- Select values from actual table Select EmpID, FirstName, Salary from dbo.Employee -- Statement 2: -- delete records from table variables -- where salary is less then passed parameter Delete from @Emp where Salary< @Salary -- Return final values return End -- Call the function to get value Select * from fnMultiEmployee(19000) EmpID FirstName Salary 1 Ryan 22000 3 Black 19000 4 Rosey 19000

Conclusion

User defined functions defined without schema-binding are always non-deterministic. Only if you specify schema-binding will SQL Server check if the function is deterministic.. The nice thing is that you can easily put frequently used code into a function and return them as a column in a result set. We might use a function for a parameterized list of somethings. Functions are a good idea in SQL Server. 
They are faster and can be quite powerful. They are also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. So though they can be helpful in simplify some logic, they can also be a performance bottleneck if they're not used properly.

Learn more on another feature of SQL as:

1. SQL - Stored Procedure
2. SQL - Create Stored Procedure
3. SQL - Execute Stored Procedure
4. SQL - Alter Stored Procedure
5. SQL - Views
6. SQL – Stored Procedure Vs Ad-Hoc (In-Line)
7. SQL - Merge Statement
8. SQL - Functions
9. SQL - Cursors
10. SQL - Logical IIF() Function
11. SQL - ISNULL Function
12. SQL - CONCAT() function
13. SQL - Stuff() Function