250+ TOP MCQs on User Defined Functions and Answers

SQL Server Multiple Choice Questions on “User Defined Functions”.

1. User defined function in SQL Server can return ____________
a) Scalar value
b) Set of values
c) Result set
d) All of the mentioned

Answer: d
Clarification: SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value.

2. Point out the wrong statement.
a) You can create the function once, store it in the database
b) CLR functions offer significant performance advantage over Transact-SQL functions for computational task
c) Function is a database object in Sql Server
d) None of the mentioned

Answer: d
Clarification: Function is a set of sql statements that accepts only input parameters, perform actions and return the result.

3. Function cannot be used for __________ statement.
a) Create
b) Drop
c) Select
d) Insert

Answer: d
Clarification: We can’t use function to Insert, Update, Delete records in the database table(s).

4. The benefit of using user-defined functions in SQL Server are ____________
a) They allow modular programming
b) They can increase network traffic
c) They allow slower execution
d) All of the mentioned

Answer: a
Clarification: User-defined functions can be modified independently of the program source code.

5. Point out the wrong statement.
a) Transact-SQL user-defined functions in queries can only be executed on a multiple thread
b) User-defined scalar functions return a single data value of the type defined in the RETURNS clause
c) User-defined table-valued functions return a table data type
d) All of the mentioned

Answer: a
Clarification: Transact-SQL user-defined functions in queries can only be executed on a single thread (serial execution plan).

6. Syntax for creation of user defined function is ____________
a) CREATE FUNCTIONS
b) CREATE FUNCTION
c) CREATE FUNC
d) All of the mentioned

Answer: b
Clarification: If a CREATE FUNCTION statement produces side effects against resources that do not exist when the CREATE FUNCTION statement is issued.

7. How many types of user defined function is provided by SQL Server?
a) 2
b) 3
c) 4
d) 5

Answer: b
Clarification: User Defined functions are created by the user in the system database or in the user-defined database and there are three types of user-defined functions.

8. Which of the following is not a User defined function?
a) Max()
b) Scalar Function
c) Inline Table-Valued Function
d) Multi-Statement Table-Valued Function

Answer: a
Clarification: max() is system defined aggregate function.

9. Which of the following does not return a table variable?
a) System-defined function
b) Scalar Function
c) Inline Table-Valued Function
d) Multi-Statement Table-Valued Function

Answer: b
Clarification: User-defined multi-statement table-valued function returns a table variable as a result of actions performed by the function.

10. User defined scalar function can return only __________ data type values.
a) Numerical
b) String
c) Float
d) All of the mentioned

Answer: d
Clarification: We return any data type value from function.