Wednesday, October 6, 2010

Simple Questions on Stored Procedures

The following mind map diagrammatically briefs about stored procedures. For an in-depth learning about the concepts, do search books on-line topics for stored procedures.




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:

5 comments:

  1. 1. A

    USE 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'

    ReplyDelete
  2. 1. A
    2. C
    3. A
    4. A & D
    5. D
    6. A
    7. C

    ReplyDelete
  3. Arun, thanks for detailing with example.

    ReplyDelete
  4. I think the current content of this site is being of use for bambinos.I think you can segregate the sections like
    a)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...

    ReplyDelete
  5. Sure Babu,
    We 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.

    ReplyDelete