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?
A.
CREATE TABLE oneTable
B.
CREATE TAB1E ##oneTable
C.
CREATE TABLE #oneTable
D.
DECLARE goneTable TABLE
Correct answer: D
http://blog.sqlauthority.com/2010/02/18/sql-server-plan-recompilation-and-reduce-recompilation-performance-tuning/
The answer is C.
A. CREATE TABLE oneTable doesn´t create a temporary table.
B. CREATE TAB1E ##oneTable definitely is wrong because it will make the table be accessible by other sessions.
C.CREATE TABLE #oneTable will make the table accessible for just one session, that should be the session calling the stored procedure.
D. DECLARE goneTable TABLE is missing the @ preceding the name of the variable.
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/522d302a-857f-4730-b49f-cca7fb236912/is-it-necessary-to-clean-up-drop-temporary-tables-in-stored-procedures?forum=transactsql
https://msdn.microsoft.com/en-us/library/aa258255(SQL.80).aspx
Correct is D. Missing @ it’s a typo, there is a ‘g’.
It’s not a c, becouse “Only usp1 will be allowed to access the table.”
To the # tables, there is access from all other procs called on the same connection.
If I see “DECLARE goneTable TABLE” in the exam, I definitely ain’t picking that! Microsoft ain’t deliberately putting its shit on me, cos there’s nothing known as ‘goneTable’!! If it’s supposed to be “DECLARE @oneTable TABLE”, then they better have their shit together.
Meanwhile, I believe the answer is option C (CREATE TABLE #oneTable)
D is corect
You cannot call on the same connection other proc in parralell. Moreover @table does not met the requirement for “The solution must minimize
the need to recompile the stored procedure.”
http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/18/11-0-temporary-tables-table-variables-and-recompiles.aspx
There we have:
Notice that even though the temporary table is re-created each time the stored procedure is executed, we do not recompile the stored procedure each time. This is because the temporary table is referenced in the plan by name and not by ID if they are created in the same module. Since the temporary table is re-created each time with the same name, the same compiled plan is re-used.
So i think shoud be C
thanks Grako very interesting article
D – Table var is best option.
•A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. ***The table can be referenced by any nested stored procedures executed by the stored procedure that created the table.****
Hello,
C
I would go for C for the same reason as Skippo.
D is missing the @ to be taken into account.
a+,=)
-=Clement=-
#table – local temp table which is used only for the current connection
##table – global temp table which can be used for any connection
Correct Answer is: C