Questions:
1. Which of the following commands, a stored procedure can execute?
(a) USE
(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?
(a) Recompile
(b) Encryption
(c) Execute As
(d) Varying
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
BEGIN TRY
BEGIN TRANSACTION
DELETE From dbo.Users
WHERE UserID = @UserID
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @errMsg nvarchar(300)
SELECT @errMsg = ERROR_MESSAGE()
END CATCH
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.
Show Answers:
1. A
ReplyDeleteUSE can be used inside stored procedure within a dynamic SQL as follows:
CREATE PROCEDURE Testproc
@ClientDB varchar(100)
AS
BEGIN
declare @sqlstr varchar(8000)
set @sqlstr = ' use ' + RTRIM(@ClientDB)
set @sqlstr = @sqlstr + ' SELECT * FROM User'
print @sqlstr
Exec (@sqlstr)
END
exec Testproc 'RTSDev'
1. A
ReplyDelete2. C
3. A
4. A & D
5. D
6. A
7. C
Arun, thanks for detailing with example.
ReplyDeleteI think the current content of this site is being of use for bambinos.I think you can segregate the sections like
ReplyDeletea)for basic level
b)deep analysis on specific topic
c)production related issues which you faced
d)DB maintanance and it's types of backup's
e)advanced features in the new version like that...
Sure Babu,
ReplyDeleteWe will drive our discussions as you said. You can find many SQL articles in web, but our blog focus mainly on questions that helps for your preparation for Microsoft certifications, Interviews and daily routines.