Exam 70-761 topic 1 question 92 discussion

Actual exam question from Microsoft's 70-761

Question #: 92
Topic #: 1

You are building a stored procedure that will update data in a table named Table1 by using a complex query as the data source.
You need to ensure that the SELECT statement in the stored procedure meets the following requirements:
✑ Data being processed must be usable in several statements in the stored procedure.
✑ Data being processed must contain statistics.
What should you do?

A. Update Table1 by using a common table expression (CTE).
B. Insert the data into a temporary table, and then update Table1 from the temporary table.
C. Place the SELECT statement in a derived table, and then update Table1 by using a JOIN to the derived table.
D. Insert the data into a table variable, and then update Table1 from the table variable.

Show Suggested Answer

Suggested Answer: B 🗳️
Temp Tables...
Are real materialized tables that exist in tempdb
Have dedicated stats generated by the engine

Can be indexed -

Can have constraints -
Persist for the life of the current CONNECTION
Can be referenced by other queries or subproce
Incorrect Answers:
A: CTEs do not have dedicated stats. They rely on stats on the underlying objects
C: Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
References:
https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx https://dba.stackexchange.com/questions/13112/whats-the-difference-between-a-cte-and-a-temp-table

by vermeilyn at May 21, 2020, 12:13 a.m.

Comments

Submit Cancel

vermeilyn

5 years, 1 month ago

Why can't you use table variable?

upvoted 1 times

vermeilyn

5 years, 1 month ago

nvm, temporary table is more better with statistics

upvoted 8 times

...