Developing Microsoft SQL Server Databases — Question 2

You are creating a stored procedure named usp1. Usp1 will create a table that will be used during the execution of usp1. Only usp1 will be allowed to access the table.
You need to write the code required to create the table for usp1. The solution must minimize the need to recompile the stored procedure.
Which code segment should you use to create the table?

Answer options

• A. CREATE TABLE oneTable
• B. CREATE TABLE ##oneTable
• C. CREATE TABLE #oneTable
• D. DECLARE oneTable TABLE

Correct answer: B

Explanation

The correct option is B, as creating a global temporary table with '##' allows it to be accessed by the stored procedure usp1 while minimizing recompilation. Option A creates a standard table that would require more careful management of permissions, while C creates a local temporary table that would not be accessible outside of the procedure. Option D uses a table variable, which is not suitable for the intended purpose of creating a table for use during the execution of the stored procedure.