Stored Procedure vs. Transact-SQL- Quiz Explanation

The correct answers are indicated below, along with the text that explains the correct answers.
 

1. What makes stored procedures faster than sending a Transact-SQL statement to SQL Server?
Please select the best answer.
  A. They are not faster.
  B. The query plan is compiled and cached on the server.
  C. The Transact-SQL statements are shorter.
  D. There are less commands available, so parsing time is shorter.
  The correct answer is B.
A stored procedure is compiled and its query plan is cached. A is incorrect because stored procedures are faster. C is incorrect because Transact-SQL statements do not have to be shorter in stored procedures. D is incorrect because the same number of commands are available to both.

2. Alison wants to make sure that her query plan is guaranteed to be up to date when executing a stored procedure. She does not mind if it takes longer to execute that stored procedure. How can she ensure this?
Please select the best answer.
  A. Use the WITH RECOMPILE option when creating the stored procedure.
  B. Use the WITH UPDATE_QUERY_PLAN option when creating the stored procedure.
  C. Run the RECOMPILE Transact-SQL statement to force a recompile.
  D. Query plans are always up to date, so this is not necessary.
  The correct answer is A.
The WITH RECOMPILE option guarantees that a new query plan is generated each time the stored procedure is executed. B is incorrect because there is no such option as WITH_UPDATE_QUERY_PLAN. C is incorrect because there is no such RECOMPILE Transact-SQL statement. D is incorrect because the query plan is stored the first time the stored procedure is executed.

3. How can you create an extended stored procedure?
Please select the best answer.
  A. You cannot create an extended stored procedure because they come preinstalled with SQL Server 2012.
  B. With the CREATE EXTENDED PROCEDURE Transact-SQL statement.
  C. With the CREATED PROCEDURE EXT Transact-SQL statement.
  D. With Visual C++.
  The correct answer is D.
Extended stored procedures need to be created in a Windows development language, such as Visual C++. A is incorrect because you can create extended stored procedures.
B is incorrect because there is no CREATE EXTENDED PROCEDURE Transact-SQL statement. C is incorrect because there is no CREATE PROCEDURE EXT Transact-SQL statement.

4. If you wish to return a value from a stored procedure into a parameter, which of the following calls would you use?
Please select the best answer.
  A. EXECUTE usp_TrimSpace 5
  B. EXECUTE usp_TrimSpace 5 OUTPUT
  C. EXECUTE usp_TrimSpace @val OUTPUT
  D. EXECUTE usp_TrimSpace @val
  The correct answer is C.
You must use a variable and the OUTPUT keyword to return a value into a variable. A is incorrect because you cannot use a literal and you must specify the OUTPUT keyword. B is incorrect because you cannot use a literal with the OUTPUT keyword. D is incorrect because the OUTPUT keyword is not specified.

5. Consider the following Transact-SQL statement fragment from a stored procedure.
What happens if an error occurs during the INSERT statement? 
INSERT INTO Temp (Col1)

VALUES @TempVal

PRINT @TempVal

IF @@error <> 0

  
RETURN 1

ELSE
  
RETURN 0

…MORE CODE BELOW…

Please select the best answer.
  A. The stored procedure continues to execute more code below.
  B. The stored procedure exits with a return value of 1.
  C. The stored procedure exits with a return value of 0.
  D. The stored procedure exits with no return value.
  The correct answer is C.
Because the @@error global function checks the previous Transact-SQL statement, the PRINT statement does not actually produce an error and therefore causes a return value of 0 to be produced. A is incorrect because at least one of the RETURN statements will be executed before the rest of the code is executed. B is incorrect because the error didn’t occur on the PRINT statement, it occurred on the INSERT statement. D is incorrect because explicit return values are specified in the question.


SQL-Server 2019