User-Defined Functions in Microsoft SQL Server

User Defined Function :- 

SQL Server's own list of available functions is not enough for database developers to use in their applications, especially when it comes to business logic, complex algorithmic calculations and custom actions. In this light, beginning with SQL 2000, SQL Server allows database developers to create their own routines, which can accept parameters, perform custom actions and return results. These routines are called user-defined functions.

Initially SQL Server only allowed user-defined functions in T-SQL. Following CLR integration in the SQL Server engine, the later versions of SQL Server (including SQL Server 2008 R2) allow users to create user-defined functions (called UDFs from here on) in a managed code, in addition to T-SQL.

With UDFs, database developers can express their business logic in a form they know best and get their business logic executing in the SQL engine from where it can be called in their SQL queries.

Benefits of User-defined Functions

Below are the most visible benefits of user-defined functions.

• Extend programming richness via user code - Your database can now do more than only process data. With UDFs, databases can now also validate business logic and do custom actions.
• Faster performance than T-SQL functions for computational logic - T-SQL functions are great at processing data. When it comes to business logic and custom algorithms, UDFs can do a much better job. UDFs work akin to stored procedures.
• Optimizes network traffic - UDFs allow data manipulation such that you don't need a lot of talking to the database and you can optimize your results based on your business logic within the database. This reduces network bandwidth utilization.
• Reusable code, which can be called akin to stored procedures with business logic.

Limitations of User-defined Functions

When called in SQL queries, UDFs are only executed in a single thread. They follow serial execution plan. Hence, if the business logic inside UDFs would work better in a multi-threaded environment, it would be better to not implement it as a user-defined function, since it would lead to over-all performance degradation.

UDFs cannot change database state so there might be usage constraints if you are attempting to change the database information inside your business logic.

Where are User Defined Functions Used

The following is a list of most common usage area of UDFs.

• In T-SQL statements, such as select
• Inside the definition of another User-defined function
• As a replacement of stored procedures
• For defining automatic values for a column in a table

Structure of User-defined Function

UDFs are composed of two parts:

1. Header
2. Function body

The header contains the function name, input parameter info (name and type), and return parameter info (name and type). The function body contains the logic. It contains one or more T-SQL statements that contain the logic and it can also contain a reference to a .NET assembly (in case of a CLR UDF).

Types of User-defined Functions

- Scalar Functions – The function which returns a Scalar/Single value.
- Table Valued Functions – The function which returns a row set of SQL server Table datatype. Table Valued Functions can be written as –

• Inline Table
• Multi-statement Table

I have preconfigured Northwind database on my SQL Server instance. We will be using the following tables for creating different User Defined Functions –

• Customers
• Employees
• Orders
• Order Details
• Products

Let’s start querying the above table. Open a new Query window and write the following commands – 

Scalar Function

We will now create a scalar function, which returns the number of orders placed by a given customer. Write the following code in your query pad – 



The above function returns an integer value. To test this function, we will write some code as shown below – 



Let us see another example which will fetch the number of orders processed by an employee for a given year. Write the following function in our query pad – 



We will test this function with different years for an employee as shown below – 

Table Valued Functions

Now let’s try an Inline Table valued function. Inline Table valued functions can return a row set using SQL Server Table datatype. You cannot perform addition logic in inline table valued functions. We will fetch the product details purchased by a customer as shown below – 



To test this example we will use a select statement as shown below – 



Another example of the Inline Table Valued Function is as shown below – 



To test this function, we will use different years as shown below – 




We will now see a Multi-Statement Table Valued Function. This function can be used to perform additional logic within the function. The code is as shown below – 



To use the Multi-Statement Table Valued function, use this code – 

-----------------------------------------------------------------

Another Example of Table Valued Function

Inline table Value:

Its returns a table data type and is an extravagant alternative to a view as the user-defined function, it will take parameters into a T-SQL select command and in synopsis provide us with a parameterized, non-updateable view of the underlying tables.

CREATE FUNCTION EmployeeByCountry

(@Country varchar(30))

RETURNS TABLE

AS

RETURN

  SELECT dbo.WhichCountry(tblEmployeeCity.vCity)as tblEmployeeFullInformation,

         tblEmployeeCity.*

  FROM tblEmployeeCity

  WHERE dbo.WhichCountry(tblEmployeeCity.vCity)= @Country

GO

Above code will create a table with EmployeeByCountry name. And when this table will execute with T-SQL select command then we also need to pass a parameter with the table name below is example:

select * from EmployeeByCountry('India')

Output:

Multi-statement Table-valued.

CREATE FUNCTION Employee

( @ID varchar(50))

RETURNS

@EmployeeInfo table (

Emp_name Varchar(50),

Emp_City Varchar(20)

)

AS

BEGIN

INSERT INTO @EmployeeInfo SELECT eName,vCity FROM tblEmployeeCity WHERE vEmpID = @ID

IF @@ROWCOUNT = 0

BEGIN

INSERT INTO @EmployeeInfo VALUES ('','No Enployee Found')

END

RETURN

END

GO

Above code will create a virtual table which is shows the employee information as Employee Name and Employee City.

Execute:

SELECT * FROM Employee('E001')

 ---------------------------------------------------------------

There are couple of limitations you must consider before creating User Defined Functions. Some of them are as shown below – 

• You cannot modify the state of the database using UDFs
• Unlike Stored Procedures, UDF can return only one single result set
• UDF does not support Try-Catch, @ERROR or RAISERROR function

Summary – User-defined functions are routines which perform calculations, receive one or more parameters and return either a scalar value or a result set. In this article, we saw how to create User Defined Functions. We also saw how to use Scalar functions and Table Valued Functions [Inline Table Valued Functions and Multi-Statement Table Valued Functions].