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).
- 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.
- These functions are created by the user or programmer.
- Functions are taking some parameters, do some processing and returning some results back
Some functions also do not take any parameters
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
- Scalar functions
- Inline table-valued functions
- 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:
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
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
- Inline Table-valued Function
- 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)
- We specify Table as the return type instead of any scalar data type.
- The function body is not closed between BEGIN and END block
- 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.
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.
- The function must return a value but in Stored Procedure, it is optional (Procedure can return zero or n values).
- Functions can have only input parameters whereas Procedures can have input/output parameters.
- A Function can be called from Procedure whereas Procedures cannot be called from Function
- From a procedure, we can call another procedure or a function whereas from a function we can call another function but not a procedure.
- The procedure allows SELECT as well as DML (INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
- Procedures cannot be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
- Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
- Functions that return tables can be treated as a row set. This can be used in JOINs with other tables
- The exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
- We can go for Transaction Management in Procedure whereas we can’t go in Function.
- We call a procedure using EXECUTE/ EXEC command whereas a function is called by using SELECT command only.
- 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
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
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.
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.