Which replacement techniques should you use?

HOTSPOT

You have the following subqueries: Subquery1, Subquery2, and Subquery3.

You need to replace the three subqueries with named result sets or temporary tables.

The following requirements must be met:

Which replacement techniques should you use? To answer, select the appropriate options in the answer area.

View Answer

Answer:

Explanation:

Subquery1: common table expression (CTE)

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

Subquery2: global temporary table

Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

Subquery3: local temporary table

Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server.

References:

https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

https://technet.microsoft.com/en-us/library/ms186986.aspx