This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “User Defined Functions – 2”.
1. Which of the following is not a limitation of user defined function?
a) User-defined functions cannot call a stored procedure, but can call an extended stored procedure
b) User-defined functions cannot make use of dynamic SQL or temp tables. Table variables are allowed
c) SET statements are allowed in a user-defined function
d) The FOR XML clause is not allowed
View Answer
Explanation: SET statements are allowed in a user-defined function.
2. Point out the correct statement.
a) User-defined functions cannot be used to perform actions that modify the database state
b) User-defined functions cannot contain an OUTPUT INTO clause that has a table as its target
c) User-defined functions can not return multiple result sets. Use a stored procedure if you need to return multiple result sets
d) All of the mentioned
View Answer
Explanation: Transact-SQL errors that cause a statement to be canceled and continue with the next statement in the module (such as triggers or stored procedures) are treated differently inside a function.
3. Which of the following statements cannot be included in the definition of a Transact-SQL user-defined function?
a) GET CONVERSATION GROUP
b) MOVE CONVERSATION
c) RECEIVE
d) All of the mentioned
View Answer
Explanation: Most of the Service broker statements cannot be included in the definition of a Transact-SQL user-defined function.
4. CREATE FUNCTION requires __________ permission in the database.
a) ALTER
b) DROP
c) DELETE
d) All of the mentioned
View Answer
Explanation: ALTER permission is required on the schema in which the function is being created.
5. Point out the wrong statement.
a) If the function specifies a user-defined type, requires EXECUTE permission on the type
b) If the function specifies a user-defined type, requires READ permission on the type
c) If the function specifies a user-defined type, requires ALTER permission on the type
d) None of the mentioned
View Answer
Explanation: Function creation requires ALTER and EXECUTE permission.
6. Which of the following is the most visible benefit of user-defined function?
a) Slow performance
b) Bad optimized queries
c) Faster performance
d) All of the mentioned
View Answer
Explanation: T-SQL functions are great at processing data. When it comes to business logic and custom algorithms, UDFs can do a much better job. UDFs work was akin to stored procedures.
7. UDFs in SQL Server is composed of _______ parts.
a) 1
b) 2
c) 3
d) 4
View Answer
Explanation: UDFs are composed of two parts:Header and Function body.
8. When to choose scalar function over inline table valued function?
a) You want a single value as the result
b) One or more row’s worth of data is expected
c) You want multiple values as result
d) All of the mentioned
View Answer
Explanation: For an inline scalar function, there is no function body; the scalar value is the result of a single statement.
9. Which of the user defined function will be preferred for adding two numbers?
a) Scalar
b) Inline table valued
c) Max()
d) Min()
View Answer
Explanation: We are can create a Scalar UDF AddTwoNumbers which accepts two input parameters @a and @b and returns output as the sum of the two input parameters.
10. The ability to create a user-defined function (UDF) is a new feature added to ________________
a) SQL Server 6.5
b) SQL Server 7.0
c) SQL Server 2000
d) None of the mentioned
View Answer
Explanation: A user-defined function is a database object that encapsulates one or more Transact-SQL statements for reuse. Developers have been asking Microsoft to add this feature for several versions of the product.
Sanfoundry Global Education & Learning Series – SQL Server.
To practice all areas of SQL Server, here is complete set of 1000+ Multiple Choice Questions and Answers.
- Apply for Programming Internship
- Check Information Technology Books
- Check SQL Server Books
- Practice Programming MCQs
