This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “Stored Procedure – 2”.
1. Reasons for consideration of the stored procedure can be?
a) With which tables will you have to work? Does it make sense to create a VIEW first?
b) How often will this procedure actually be used?
c) Do you want to view data in the database (SELECT), insert new records (INSERT INTO), or do I want to change an existing record (UPDATE)?
d) All of the mentioned
View Answer
Explanation: A stored procedure is an already written SQL statement that is saved in the database.
2. Point out the correct statement.
a) Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed
b) A stored procedure is a group of Transact-SQL statements pre compiled into a single execution plan
c) Stored procedures cannot improve performance
d) All of the mentioned
View Answer
Explanation: Stored procedures assist in achieving consistent implementation of logic across applications.
3. __________ takes no parameters and returns all kinds of interesting information.
a) sp_monitor
b) sp_on
c) sp_status
d) none of the mentioned
View Answer
Explanation: sp_monitor displays statistics about the instance of SQL Server in which it runs.
4. Which of the following has support for transaction?
a) sp_monitor
b) sp_bindsession
c) sp_status
d) all of the mentioned
View Answer
Explanation: sp_getbindtoken also can be used for transactions.
5. Point out the wrong statement.
a) Support for transactions is one of the most important reasons to use database engines such as SQL Server
b) BASE support
c) ACID support
d) All of the mentioned
View Answer
Explanation: Stored procedures don’t have support for BASE.
6. __________create the removable database.
a) sp_certify_removable
b) sp_certify
c) sp_removable
d) all of the mentioned
View Answer
Explanation: You can’t use CREATE DATABASE or Enterprise Manager to create the removable database. You’ll need to run sp_create_removable with sysadmin privileges.
7. Which of the following stored procedure is used for error messages?
a) sp_addmessage
b) sp_dropmessage
c) sp_altermessage
d) all of the mentioned
View Answer
Explanation: Calling sp_addmessage is easy for raising errors.
8. _____________ sets procedure options in SQL Server.
a) sp_procoption
b) sp_dropmessage
c) sp_altermessage
d) sp_executesql
View Answer
Explanation: sp_procoption sets exactly one option to configure a stored procedure for auto execution when SQL Server starts up.
9. __________ is used to build the code dynamically and execute it.
a) sp_addmessage
b) sp_dropmessage
c) sp_altermessage
d) sp_executesql
View Answer
Explanation: You can execute SQL dynamically using either the EXECUTE T-SQL statement or the sp_executesql stored procedure. sp_executesql is the more flexible of the two though because it supports using parameters.
10. Stored procedure used to remember the exact number of bytes is _____________
a) sp_datatype
b) sp_datatype_info
c) sp_info
d) none of the mentioned
View Answer
Explanation: sp_datatype_info returns just about everything you need to know about the datatypes available in any SQL Server database.
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.
If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]
- Apply for Programming Internship
- Check SQL Server Books
- Practice Programming MCQs
- Check Information Technology Books
