1. Which of the following commands, a stored procedure can execute?
(b) Set Showplan_Text on
(c) Set Showplan_All on
(d) Both (b) and (C)
(e) None of the above.
2. You have noticed that a stored procedure is recompiled on each execution. The cause of recompilation is a simple query statement. How will you optimize the performance of your stored procedure with minimum effort?
(a) Create an additional stored procedure, and include the query that causes the recompilation. Call the new stored procedure from the new one.
(b) Add the RECOMPILE query hint to the query statement that causes the recompilation.
(c) Modify your stored procedure, and include the WITH RECOMPILE option in its definition.
(d) Use the sp_recompile system stored procedure to force the recompilation of your stored procedure the next time it runs.
3. Which one of the following option regenerates the query plan each time a stored procedure is executed?
(c) Execute As
4. Which of the following can be used to recompile a stored procedure everytime it is running? (Choose all that apply.)
(a) Modify your stored procedure, and include the WITH RECOMPILE option in its definition.
(b) Add the RECOMPILE query hint to one of the stored procedure statements.
(c) Use the sp_recompile system stored procedure.
(d) Specify the WITH RECOMPILE option when you execute the stored procedure.
5. Your RTSdev database alerts you that the transaction log file is almost full. You suspect that one of the stored procedures has left a transaction open. Which one of the following can be used to ensure it?
(a) Execute DBCC TLOGFULL against the RTSdev database.
(b) Execute DBCC OPENTRAN against the temp database.
(c) Execute DBCC TLOGFULL against the temp database.
(d) Execute DBCC OPENTRAN against the RTSdev database.
6. The following script is used to create a stored procedure:
DECLARE @UserID int
DELETE From dbo.Users
WHERE UserID = @UserID
DECLARE @errMsg nvarchar(300)
SELECT @errMsg = ERROR_MESSAGE()
When execute the SP, you observe that it leaves open transactions. How will you modify the stored procedure?
(a) Add a ROLLBACK TRANSACTION to the CATCH block.
(b) Add a ROLLBACK TRANSACTION to the TRY block.
(c) Add a COMMIT TRANSACTION to the CATCH block.
(d) Add a COMMIT TRANSACTION to the TRY block.
7. If you need to create a stored procedure to perform some calculation-intensive operations and then return the results as quickly and efficiently as possible. How will you achieve that?
(a) Use parameters within the stored procedures.
(b) Use a TRY CATCH block within the stored procedure.
(c) Use a CLR-integrated stored procedure.
(d) Create the stored procedure so it recompiles each time it is run.