SQL Server Functions Tips And Tricks

Important concepts related to the function in SQL Server are:

• Deterministic and Nondeterministic function
• Encrypting function
• Schema binding function

Deterministic Function

Deterministic function always returns the same result, any time they are called with the specific set of the input values and the same state of the database.

Example: Count (), Power (), Sum (), Avg() and Square().

select * from Employee

If you locked this Employee table, there are two rows in this table. Execute the code, given below:

select COUNT(*) TotalEmployee from Employee

Count function returns the total number of the records in the table in the employee. There are two records in the table, so count function returns two; no matter how many times you execute this query, it will return two. Now, if I change the database state; it means to insert new records or delete records. Afterwards, try the count command and it will change.

Let’s look at another example, Square() Function, which returns the square of the given number.

select SQUARE(3) [Square]

If we execute this query, it will return nine -- every time you'll get nine as an output.

Nondeterministic Function

Nondeterministic function is just the opposite of the Deterministic function. Nondeterministic function may return a different result each time, it is called with a specific set of the input values, even if the database state remains the same.

Example: Getdate () and current_timestamp

select current_timestamp

Every time, we execute it, it returns a different output.

Rand () Function

Rand () Function is a non-deterministic function, but if you provide the seed value, the function becomes Deterministic, as the same value  returns the same seed value.

Example: When I say select Rand, this function expects an integer value, as input parameter is known as a seed value.

Let’s pass one as a seed value and if anyone executes this query, it returns the same output, every time.

select RAND(1)

We can see that this function now works as a Deterministic function.

On the other hand, if we do not provide the seed value, Rand function works as a Non-Deterministic function, given below:

select RAND()

Every time this function returns a different value.

Encrypting a function definition using WITH ENCRYPTION OPTION

If we want another user to not see your created function and not be able to modify it, it is possible with the encryption, as once its encrypted, you cannot view the text of the function using the “sp_helptext” system store procedure. If you try to, you will get a message that states ‘The text for object is encrypted.’

Example: Let’s create a scalar function,

CREATE FUNCTION FN_GETDATE()

RETURNS DATETIME

AS

BEGIN

RETURN (SELECT GETDATE())

END

This function returns Current date and with the help of sp_helptext, we can see definition of the function:

If you do not want anyone to see your function definition, you create a function with the encryption.

CREATE FUNCTION FN_GETDATE()

RETURNS DATETIME

WITH ENCRYPTION

AS

BEGIN

RETURN (SELECT GETDATE())

END

Now, you try to see the definition through sp_helptext

It will give the message: “The text for object 'FN_GETDATE' is encrypted.”

It applies to the Object Explorer.

Creating a function WITH SCHEMABINDING OPTION

What do we mean by Schema binding? This is very important and the best practice is when creating a function. Specify that the function is bound to the database object; i.e., the reference. When schemabinding is specified, the base object cannot be modified in any way that affects the function definition. The function definition itself must first be modified or dropped to remove the dependency on the object; i.e., to be modified.

Example: Now, create a function,  get employee, with the name of the given employee id, shown below:

create FUNCTION FN_GETNAME(@id int)

RETURNS DATETIME

AS

BEGIN

RETURN (select name from Employee where ID=@id )

END

Select dbo.FN_GETNAME(1)

This query returns the employee name, where id =1. Now, we delete the table employee ID and try to execute the code, given below:

Select dbo.FN_GETNAME(1)

It gives an error - Invalid object name “employee”.

There is now a problem because both are different objects; one is a table and another is a function.

Your function depends on the table. To prevent this type of change, we use schemabinding, given below:

CREATE FUNCTION dbo.FN_GETNAME(@id varchar(10))
RETURNS INT
WITH RETURNS NULL ON NULL INPUT,
  SCHEMABINDING AS
BEGIN
  DECLARE @tempID INT
  SELECT @tempID = ID
  FROM dbo.employee
  WHERE id = @id;

  RETURN @tempID;
END;

When we create a function with schemabinding and change the table structure or delete the table, it enforces that first change function.