SQL Server Functions

May 6, 2013 | balvvant2006 | Uncategorized

Introduction

Functions are kind of reusable components/objects in SQL-Server. It is a set of SQL statements that accepts only input parameters, perform actions and return the result. Function can return only single value or a table value. We can’t use function to Insert, Update, Delete records in the database tables.

SQL Server’s functions are a valuable addition when used wisely. A function, in any programming environment, lets you encapsulate reusable logic and build software of pieces that can be reused and put together in a number of different ways to meet the needs of the users. Functions hide the steps and the complexity from other code. Functions can hide complexity from users and turn a complex piece of code into a re-usable commodity.

SQL Server functions adhere closely to the mathematic definition of a function i.e. a mapping of inputs to outputs, without have side effects. A function with inputs x and y cannot both return x + y and modify the original value of y. As a matter of fact, that function couldn’t even modify y: it is only able to return a new value.

Some highlights of Functions

Function must return a value.
Functions can have only input parameters for it.
Function allows only SELECT statement in it.
Function can be embedded in a SELECT statement.
Functions can be used in the SQL statements anywhere in the WHERE/ HAVING/ SELECT section.
Functions that return tables can be treated as another row-set. This can be used in JOINs with other tables.
Inline Function can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.
Try-catch block cannot be used in a Function.
Transactions are not allowed in Functions.
Function should have at least one input parameter.
Functions can be called from Procedure.
User Defined Function can call only Extended Stored Procedure.
Like Stored Procedure, Function can be nested up to 32 levels.
User Defined Function can have upto 1023 input parameters while a Stored Procedure can have upto 21000 input parameters.
User Defined Function can’t returns XML Data Type.
User Defined Function doesn’t support set options like set ROWCOUNT etc.

Where we can use a Function?

Functions are an incredibly powerful part of SQL Server. We can use a function almost anywhere we would use a table or column. We can use a function anywhere that we can use a scalar value or a table. Functions can be used in constraints, computed columns, JOINs, WHERE clauses, in other functions or even in Stored Procedures.

Functions can be Deterministic or Nondeterministic

A deterministic function will return the same result when it is called with the same set of input parameters. Adding two numbers together is an example of a deterministic function.

A nondeterministic function, on the other hand, may return different results every time they are called with the same set of input values. Even if the state of the data in the database is the same, the results of the function might be different. The GETDATEfunction, for example, is nondeterministic.

When we create a function, SQL Server will analyze the code we’ve created and evaluate whether the function is deterministic. If our function makes calls to any nondeterministic functions, it will, itself, be marked as nondeterministic. SQL Server relies on the author of a SQL CLR function to declare the function as deterministic using an attribute. Deterministic functions can be used in indexed views and computed columns whereas nondeterministic functions cannot.

Keeping things safe: functions can be schema-bound

If we make changes to database schema after function creation, it may break the function. To prevent Functions from breaking due to schema changes we can create schema bound functions. Attempts to alter objects that are referenced by a schema bound function will fail thereafter. What does this buy us, though? Well, schema binding a function makes it more difficult to make changes to the underlying data structures that would break the functions. To create a schema-bound function we simply specify schema binding as an option during function creation, as shown below.

–Create function to get emp full name

Create functionfnGetEmployeeFullName

(

@FirstName varchar(50),

@LastName varchar(50)

)

returns varchar(101)

– this statement is used to make the function schema bound

WITH SCHEMABINDING

Begin return (Select @FirstName + ‘ ‘+ @LastName);

End

Behavior if case of NULL

Whenever our function receives NULLinput values, SQL Server will go ahead and run the code in the function and evaluate all of the parameters passed in, even if one of those parameters is a NULL value, and so the output of the function is NULL. This is a waste of processor cycles and we need to avoid this unnecessary work. We could check every parameter that is passed into a function, but that is a lot of code to maintain. If you’re thinking, “there has to be a better way” then you’re absolutely right. When we create the function we can use the RETURNS NULL ON NULL INPUT option, which will cause SQL Server to immediately return NULL if any parameters in the function are NULL-valued. Users of SQL Server 2000 and earlier are out of luck, though, as this feature was introduced in SQL Server 2005.

Types of Function

System Defined Function

SQL Server has many built in Functions which can be used for carrying out variety of operations and calculations. We have two types of system defined function in Sql Server.

Scalar Function

Scalar functions operate on a single value and returns a single value. Some examples of useful SQL Server Scalar Functions are:

Scalar Function	Description
abs(-10.67)	This returns absolute number of the given number means 10.67.
rand(10)	This will generate random number of 10 characters.
round(17.56719,3)	This will round off the given number to 3 places of decimal means 17.567
upper(‘dotnet’)	This will returns upper case of given string means ‘DOTNET’
lower(‘DOTNET’)	This will returns lower case of given string means ‘dotnet’
ltrim(‘ dotnet’)	This will remove the spaces from left hand side of ‘dotnet’ string.
convert(int, 15.56)	This will convert the given float value to integer means 15.

Aggregate Function

Aggregate functions operate on a collection of values and return a single value. Some examples of useful SQL Server Aggregate Functions are:

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.
count()	This returns no of counts from a collection of values.

User Defined Functions

These functions are created by user in database. There are three types of user defined functions.

Scalar Function

Scalar functions return a single value. We can use a scalar function “anywhere that a scalar expression of the same data type is allowed in T-SQL statements“. All data types in SQL Server are scalar data types, with the exception of TEXT, NTEXT, ROWVERSION , and IMAGE.

Let’s go through following example:

–Create a table

CREATE TABLE Employee

(

EmpID intPRIMARY KEY,

FirstName varchar(50) NULL,

LastName varchar(50)NULL,

Salary intNULL,

)

–Insert Data

Insert into Employee(EmpID, FirstName, LastName, Salary)

Values (1,‘Balvvant’,‘Bist’, 10000);

Insert into Employee(EmpID, FirstName, LastName, Salary)

Values (2,‘Gerard’,‘Dsouza’,20000);

Insert into Employee(EmpID, FirstName, LastName, Salary)

Values (3,‘Amit’,‘Pathak’,30000);

–See created table

Select * fromEmployee

–Create a scalar function to get emp full name

Create functionfnGetEmployeeFullName

(

@FirstName varchar(50),

@LastName varchar(50)

)

returns varchar(101)

Begin return (Select @FirstName + ‘ ‘+ @LastName);

end

–Call above created function

Select dbo.fnGetEmployeeFullName(FirstName,LastName) as Name, Salary fromEmployee

The output of the above SQL statement will be:

Table Valued Functions (TVFs)

Table-valued functions (TVFs) return a table instead of a single value. A table valued function can be used anywhere a table can be used – typically in the FROM clause of a query. TVFs make it possible to encapsulate complex logic in a query. For example, security permissions, business logic etc can be embedded in a TVF. Careful use of TVFs makes it easy to create re-usable code frameworks in the database.

Table-valued Functions (TVFs) differ from scalar functions in that TVFs return an entire table whereas scalar functions only return a single value. This makes them ideal for encapsulating more complex logic or functionality for easy re-use. TVFs have the additional advantage of being executed once to return a large number of rows as opposed to scalar functions which must be executed many times to return many values.

The body of a TVF can either contain just a single statement or multiple statements, but the two cases are handled very differently by the optimizer. If the function body contains just a single statement (often referred to as an “inline TVF”), then the optimizer treats it in a similar fashion to a view in that it will “decompose” it and simply reference the underlying objects and there will be no reference to the function in the resulting execution plan.

However, by contrast, multi-statement TVFs present an optimization problem for SQL Server; it doesn’t know what to do with them. It treats them rather like a table for which it has no available statistics – the optimizer assumes that it the TVF will always return one row. As a result, even a very simple multi-statement TVF can cause severe performance problems.

There are two different types of TVF functions in SQL-Server.

Inline Table-Valued Function

Inline table-valued function returns a table variable as a result of actions performed by the function. The value of table variable should be derived from a single SELECT statement.

–Create function to get employees

Create function fnGetEmployee()

returns Table

return (Select *from Employee)

–Now call the above created function
— now call the above created function

Select * fromfnGetEmployee()

Multi-Statement Table-Valued Function

Multi-statement table-valued function returns a table variable as a result of actions performed by the function. It is 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. Also, the ability to pass parameters into a T-SQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables.

Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, I can use it in the FROM clause of a T-SQL command unlike the behaviour found when using a stored procedure which can also return record sets.

–Create function for EmpID,FirstName and Salary of Employee

ALTER functionfnGetUpdatedEmployeeSal()

returns @Emp Table

(

EmpID int,

FirstName varchar(50),

Salary int

)

begin

Insert @Emp Select e.EmpID,e.FirstName,e.Salary from Employee e;

–Now update salary of first employee

update @Emp set Salary= 30000 where EmpID= 1 OR EmpID= 3;

–It will update only in @Emp table not in Original Employee table

return

end

–Now call the above created function

Select * fromfnGetUpdatedEmployeeSal()

— Note that the salaries for the employee with ID =1 and 3 are changed to 30000.

–Now see the original table. This is not affected by above function update command

Select * fromEmployee

What are the benefits of User-Defined Functions?

The benefits to SQL Server User-Defined functions are numerous. First, we can use these functions in so many different places when compared to the SQL Server stored procedure. The ability for a function to act like a table (for Inline table and Multi-statement table functions) gives developers the ability to break out complex logic into shorter and shorter code blocks. This will generally give the additional benefit of making the code less complex and easier to write and maintain. In the case of a Scalar User-Defined Function, the ability to use this function anywhere you can use a scalar of the same data type is also a very powerful thing. Combining these advantages with the ability to pass parameters into these database objects makes the SQL Server User-Defined function a very powerful tool.

TVFs have the additional advantage of being executed once to return a large number of rows (as opposed to scalar functions which must be executed many times to return many values).

One of the problems with scalar functions is that they are executed once for every row in the result set. While this is not a problem for small result sets, it becomes a problem when our queries return a large number of rows. We can use TVFs to solve this problem.

Drawbacks of Functions

The biggest drawback of SQL Server functions is that they may not be automatically inlined. For a scalar function that operates on multiple rows, SQL Server will execute the function once for every row in the result set. This can have a huge performance impact. Fortunately, with TVFs (Table Value Functions), SQL Server will call them only once, regardless of the number of rows in the result set and it’s often possible, with a bit of ingenuity, to rewrite scalar functions into TVFs, and so avoid the row-by-row processing that is inherent with scalar functions. In some cases, it might be necessary to dispense with the TVF altogether, and simply “manually inline” the function logic into the main code. Of course this defeats the purpose of creating a function to encapsulate re-usable logic.

Scalar UDFs are a fairly straightforward feature but there are some drawbacks to them, the biggest one being that, as discussed earlier, 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.

The body of a TVF can either contain just a single statement or multiple statements, but the two cases are handled very differently by the optimizer. If the function body contains just a single statement (often referred to as an “inline TVF”), then the optimizer treats it in a similar fashion to a view in that it will “decompose” it and simply reference the underlying objects (there will be no reference to the function in the resulting execution plan).