You plan to modify a procedure that contains hundreds of lines of code.
The modification must support the following guidelines:
Use only tables that are not persistent in the database.
Minimize the amount of time required to execute and recompile procedures.
You need to identify which type of table must be used to support the planned modification.
Which type of table should you identify?
A.
A system table
B.
A partitioned table
C.
A table variable
D.
A temporary table
D, sql server will reuse the compiled plan for a temporary table in case the table has the same name by the same procedure
I would opt for C. Table variable has no statistics, so by using it there will be no recompilations caused by temporary object.
Temporary table, on the other hand, has statistics, so if the number of rows will be growing during procedure execution, there can be a necessity to recompile the procdure and this is in a contradiciton with guidelines. We don’t know nothing about the number of rows that will be in temporary object so I would stick to the recompile possibilities, which are lower when one uses table variable.
C is the correct answer