SQL Server’s own list of functions is limited as far as what is available for database developers to use in their applications. Starting with SQL Server 2000, database developers were given the ability to create their own routines that accept parameters, perform custom actions and return results.
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:
- Header
- 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
Based on the return type, UDFs can be classified as scalar
functions and table-value functions.
Scalar Functions
Scalar functions are UDFs, which return a single value.
There is no function body in the case of an inline scalar
function, and the scalar value (the return value) is the result of a single
statement.
When to choose?
Scalar functions are preferred when you want a single value
as the result. Examples might be the total cost of a business trip, total
project cost in man-days or the birth date of a person.
The return type can be any valid SQL type except text,
ntext, image, cursor and timestamp.
CREATE FUNCTION dbo.CalculateTotalDaysForProject(@DevelopmentDays int, @TestingDays int) RETURNS int AS BEGIN DECLARE @totalcost int; SELECT @ totalcost = @DevelopmentDays + @ TestingDays; RETURN @totalcost; END
Listing 1 – Example of a scalar function Table-values
Functions
Table value functions
Table value functions are UDFs that, as the name suggests,
return a table.
When to choose?
Table value functions are used when one or more row’s worth
of data is expected. Examples would be all orders generated by the best
performing salesperson, etc.
CREATE FUNCTION GeneratedSales.SalesByPerson(@sales_ID int) RETURNS @SalesData TABLE ( [CustomerID] int, [SalesAmount] int, [SalesDate] date ) AS BEGIN INSERT INTO @SalesData SELECT Sales.CustomerID, Sales.SalesAmount, Sales.SalesDate from Sales Where Sales.Sales_ID = @sales_ID RETURN END
Listing 2 – Example of a table-value function
Creating User-defined Functions
User-defined functions are created by the "CREATE FUNCTION"
statement. They can be edited by using the "ALTER FUNCTION" statement
and deleted by the "DROP FUNCTION" statement.
One key thing to keep in mind is that statements within the
BEGIN..END block cannot make any changes like updates to the database. Only local
changes (objects in the local scope, such as temporarily created tables and
variables) are allowed.
Valid T-SQL statements allowed in User-defined functions
include DECLARE, SELECT, TRY…CATCH, EXECUTE, UPDATE (local only), INSERT(local
only), DELETE (local only).
Most deterministic T-SQL built-in functions can be used
inside a UDF.
CLR user defined functions
Creating a CLR UDF is a multi-stage process.
First, you define the desired function as a static method of
a class in the .NET framework language.
For example, let us create a function in C#, which lists the
count of unique salespersons that made sales in the last quarter.
using Microsoft.SqlServer.Server; using System.Data.SqlClient; public class Sales { public static int GetCountOfUniqueSalesPersons() { using (SqlConnection myConnection = new SqlConnection("context connection=true")) { myConnection.Open(); SqlCommand myCmd = new SqlCommand( "SELECT COUNT(DISTINCT SalesPersonID) AS 'UniqueSalesPersons' FROM SalesOrders", myConnection); return (int)myCmd.ExecuteScalar(); } } }
Listing 3 – CLRfunction.cs
Now compile this as a library.
C:windowsmicrosoft.netframeworkv2.0.50727csc.exe /t:library /out:CLRUDF.dll CLRFunction.cs
Next, we register this assembly with SQL using the CREATE
ASSEMBLY statement.
CREATE ASSEMBLY CLRUDF FROM 'CLRUDF.dll';
Lastly, we create a function that references the newly
registered assembly using the CREATE FUNCTION statement.
CREATE FUNCTION GetCountOfUniqueSalesPersons() RETURNS INT AS EXTERNAL NAME CLRUDF.Sales.GetCountOfUniqueSalesPersons;We can now use this newly available user-defined function.
T-SQL user defined functions
T-SQL UDFs are simpler to create as they only have SQL
statements. See above for examples of the Scalar UDF and Table-Value UDFs.Executing User Defined Functions
UDFs can be executed in a number of places as mentioned
above. Let us look at how to invoke UDFs in a simple SELECT statement. The
following will list all salespersons that made sales in the last quarter.SELECT dbo.GetCountOfUniqueSalesPersons() FROM SalesOrders where SalesOrders.QuarterStartDate = '2010-04-01' and Sales.QuarterEndDate = '2010-6-30';Summary
User defined functions provide an easy way to encapsulate
business logic and custom actions and can be used by database developers to
optimize their application behavior. I hope that this article will give you
ideas on how this feature can be used to make more performance database
applications.