SQL Server Functions Interview Questions

SQL Server Functions Interview Questions and Answers

In this article, we will discuss most frequently asked SQL Server Functions Interview Questions and Answers.

What is a function?

A function is a database object in SQL Server. Basically, it is a set of SQL statements that accept only input parameters, perform actions and return the result. The function can return only a single value or a table. We can’t use the function to Insert, Update and Delete records in the database table(s). 

  1. It is also a subprogram like a stored procedure which is defined for performing an action such as complex calculation and returns result of the action as a value.
  2. These functions are created by the user or programmer.
  3. Functions are taking some parameters, do some processing and returning some results back

For example:

Select SQUARE(3)

Output: 9

Some functions also do not take any parameters

For Example:

Select GETDATE()

So, we can say that a function can have the parameter that is optional but a function should return a value that is mandatory.

In SQL Server, there are 3 types of User Defined Functions

  1. Scalar functions
  2. Inline table-valued functions
  3. Multi-statement table-valued functions
What is a scalar function?

The functions which return a single value is known as scalar value function.

Scalar functions may or may not have parameters, but always return a single (scalar) value. The returned value can be of any data type, except text, ntext, image, cursor, and timestamp.

To create a function, we use the following syntax:

CREATE FUNCTION Function_Name(@Parameter1 DataType, @Parameter2 DataType,..@Parametern Datatype)
RETURNS Return_Datatype
AS
BEGIN
    Function Body
    Return Return_Datatype
END

The syntax for calling a function:

SELECT dbo.<FunctionName>(Value)

So, when we calling a scalar function we must supply the two-part name i.e. owner name and function name.

The dbo stands for database owner name

We can also invoke a scalar function using the complete three-part name i.e. database name. Owner name and function name.

CREATE FUNCTION SVF1(@X INT)
RETURNS INT
AS
BEGIN
  RETURN @X * @X * @X
END

To execute the function call it like below

SELECT dbo.SVF1(5)

Output: 125

What is a table-valued function?

In this case, we can return a table as an output from the function. These are again of two types

  1. Inline Table-valued Function
  2. Multi-statement table value function
Where can we use Inline Table Valued function?

Inline Table Valued functions can be used to achieve the functionality of parameterized views.

The table returned by the table-valued function can also be used in joins with other tables.

What is Inline table-valued functions?

In this case, the body of the function will have only a single select statement prepared with “RETURN” statement.

The syntax for creating a table value function

CREATE/ALTER FUNCTION <FUNCTION NAME>(@<VARIABLE NAME><DATA TYPE> [SIZE])
RETURNS TABLE
AS
RETURN <SELECT STATEMENT>

The syntax for calling a table value function:

SELECT * FROM <FUNCTION NAME> (VALUE)

  1. We specify Table as the return type instead of any scalar data type.
  2. The function body is not closed between BEGIN and END block
  3. The structure of the table that gets returned is determined by the select statement within the function.

Ex: Create a function to accept customer account number and return that customer details from the table.

CREATE FUNCTION TF1(@ACCNO INT)
RETURNS TABLE
AS
RETURN (SELECT * FROM Customers WHERE ACCNO = @ACCNO)

SELECT * FROM TF1(1021)
What are Multi-Statement Table-Valued Functions in SQL Server?

This is the same as the inline table-valued function which can return a table as an output but here the body can contain more than one statement and also the structure of the table being returned can be defined.

Syntax:

CREATE/ALTER  FUNCTION <FUNCTIONNAME> (@<PARAM><DATATYPE> [SIZE].....)
RETURNS @<TABLE VAR> TABLE (<COLUMN DEFINITIONS>)
[WITH <FUNCTION ATTRIBUTES>]
AS
BEGIN
    <FUNCTION BODY>
    RETURN
END

NOTE: In case of a multi-statement table valued function we need to define our own structure to the table being return.

--Create Inline Table value function
CREATE FUNCTION fn_ILTVF_GetEmployees()
Returns Table
as
return (select Id, Name,Cast(dateofbirth as date)as DOB from tblEmployee)

--To Execute Inline Table Valued function
Select * from dbo.fn_ILTVF_GetEmployees()

--MULTI STATEMENT TABLE VALUED FUNCTION
CREATE FUNCTION fn_MSTVF_GetEmployees()
returns @Table Table (Id int, Name varchar(50), DOB date)
as
begin
               insert into @Table
               select Id, Name,CAST(dateofbirth as date)from tblEmployee
               return
end

--To Execute multi statement Table valued Function
Select*from dbo.fn_MSTVF_GetEmployees()
Now let’s understand the differences between Inline Table Valued functions and Multi-statement Table-Valued functions

In an Inline Table Valued function, the RETURNS clause cannot contain the structure of the table, the function returns. Whereas, with the multi-statement table valued function, we specify the structure of the table that gets returned

Inline Table Valued function cannot have BEGIN and END block as it returns a single select statement, whereas the multi-statement function can have the begin and end block as it contains more than one select statement.

Inline Table-valued functions are better for performance than multi-statement table valued functions. If the given task, can be achieved using an inline table-valued function, always prefer to use them, over multi-statement table valued functions.

It’s possible to update the underlying table, using an inline table-valued function, but not possible using a multi-statement table valued function.

Updating the underlying table using an inline table-valued function: 

This query will change Sam to Sam1, in the underlying table tblEmployees. When we try to do the same thing with the multi-statement table valued function, we will get an error stating ‘Object ‘fn_MSTVF_GetEmployees’ cannot be modified.’

Update fn_ILTVF_GetEmployees() set Name=’Sam1′ Where Id = 1

Reason for improved performance of an inline table-valued function:

Internally, SQL Server treats an inline table-valued function much like it would a view and treats a multi-statement table valued function similar to how it would a stored procedure.

What are the differences between functions and procedures?

Stored Procedures are pre-compiled objects which are compiled for the first time and its compiled format is saved which executes (compiled code) whenever it is called. But Function is compiled and executed every time when it is called. 

BASIC DIFFERENCE

  1. The function must return a value but in Stored Procedure, it is optional (Procedure can return zero or n values). 
  2. Functions can have only input parameters whereas Procedures can have input/output parameters.
  3. A Function can be called from Procedure whereas Procedures cannot be called from Function
  4. From a procedure, we can call another procedure or a function whereas from a function we can call another function but not a procedure.

ADVANCE DIFFERENCE

  1. The procedure allows SELECT as well as DML (INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
  2. Procedures cannot be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
  3. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
  4. Functions that return tables can be treated as a row set. This can be used in JOINs with other tables
  5. The exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
  6. We can go for Transaction Management in Procedure whereas we can’t go in Function.
  7. We call a procedure using EXECUTE/ EXEC command whereas a function is called by using SELECT command only.
  8. Stored procedures support deferred name resolution. Example, while writing a stored procedure that uses table names, for example, table1, table2, etc. but these tables do not exist in the database is allowed during creation of the stored procedure but runtime throws error whereas functions do not support deferred name resolution.
Creating a Function with SCHEMABINDING option:

SCHEMABINDING specifies that the function is bound to the database objects that it references. When SCHEMABINDING is specified the database object cannot be modified in any way that would affect the function definition. The function definition itself must first be modified or dropped to remove dependencies on the object that is to be modified.

Suppose we create a function that depends on the table tblEmploye. If we modify the table from the database while the function is referencing to that table. After modification of the table if we execute the function it will give an error message that is Invalid Object Name tblEmployee

How to prevent this?

We need to use SCHEMABINDING option and we need to specify the two-part name of the table

CREATE FUNCTION fn_GetNameById(@Id Int)
Returns nvarchar(50)
WITH SCHEMABINDING
As
Begin
        Return (select name from dbo.tblEmployee where Id = @id)
End

Then try this

DROP TABLE tblEmployee

Message:

Cannot DROP TABLE ‘tblEmployee’ because it is being referenced by object ‘fn_GetNameById’.

Encrypting a Function definition by using WITH ENCRYPTION OPTION:

We have learned how to encrypt stored procedure text using WITH ENCRYPTION option. Along the same lines, we can also encrypt a function text, once encrypted, we cannot view the text of the function using sp_helptext system stored procedure. If we try to, we will get a message stating ‘the text for the object is encrypted’.

CREATE FUNCTION fn_GetNameById(@Id Int)
Returns nvarchar(50)
WITH ENCRYPTION
As
Begin
       return (select name from tblEmployee where Id = @id)
End

Then try this

sp_helptext fn_GetNameById

Output:

The text for object ‘fn_GetNameById’ is encrypted.

So, this proves that stored procedures support deferred name resolution, whereas functions do not. In fact, this is one of the major differences between functions and stored procedures in SQL server.

Please find below articles to learn the details about SQL Server Functions.

SQL Server Function

Inline Table Valued Function in SQL Server

Multi-Statement Table Valued Function in SQL Server

Encryption and Schema Binding Options in SQL Server Functions

Deterministic and non-deterministic functions in SQL Server

SUMMARY

In this article, I try to explain most frequently asked SQL Server Functions Interview Questions and Answers. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *