Multiple Choice Questions - Stored Procedures
1. When the stored procedure is created, the text of the stored procedure is saved in the . . . . table.
A) sys.sysconfig
B) sys.sysconfigures
C) sys.sysdatabases
D) SysComments
2. When modifying a stored procedure, dropping and recreating it is preferable to altering it, because the latter method removes any permissions.
A) True
B) False
3. To manually force a recompile of a stored procedure, use the . . . . . . system stored procedure.
A) sp_recmpl
B) sp_refresh
C) sp_recompile
D) sp_reoccur
4. If a user-defined procedure has the same name as a system procedure, the user-defined procedure might not ever execute.
A) True
B) False
5. To further reduce the amount of talk between the client and server and to improve stored procedure performance, you can eliminate the . . . . . . messages that SQL Server sends to the client API by issuing the set no count on command at the beginning of the stored procedure.
A) DONEPROC
B) DONE_IN_PROC
C) DONE
D) All of above
6. How can you check the text of the stored procedure that you created:
A)
sp_helptext
B)
SELECT OBJECT_DEFINITION (OBJECT_ID(N'Your_SP'))
C)
SELECT object_definition(object_id) as [Procedure Definition] FROM sys.objects WHERE name='Your_SP'D) All of above
7. Local variables are declared and assigned values in the stored procedure. These local variables can be referenced by any statements within the EXEC string.
A) True
B) False
8. Deleting a Stored Procedure requires . . . . . . permission on the schema to which the procedure belongs, or . . . . . permission on the procedure.
A) ALTER, CONTROL
B) EXECUTE, CONTROL
C) ALTER, EXECUTE
D) GRANT, OBJECT
9. Renaming a stored procedure will not change the name of the corresponding object name in the definition column of the . . . . . . catalog view.
A) sys.stats
B) sys.system_objects
C) sys.system_parameters
D) sys.sql_modules
10. Transact-SQL stored procedures cannot be modified to be CLR stored procedures and vice versa.
A) True
B) False
11. . . . . . . . . does not change permissions and does not affect any dependent stored procedures or triggers.
A) ALTER PROCEDURE
B) ALTER QUEUE
C) ALTER RESOURCE POOL
D) None of above
Answers