Developing SQL Databases — Question 68

You are developing an ETL process to cleanse and consolidate incoming data. The ETL process will use a reference table to identify which data must be cleansed in the target table. The server that hosts the table restarts daily.
You need to minimize the amount of time it takes to execute the query and the amount of time it takes to populate the reference table.
What should you do?

Answer options

• A. Convert the target table to a memory-optimized table. Create a natively compiled stored procedure to cleanse and consolidate the data.
• B. Convert the reference table to a memory-optimized table. Set the DURABILITY option to SCHEMA_AND_DATA.
• C. Create a native compiled stored procedure to implement the ETL process for both tables.
• D. Convert the reference table to a memory-optimized table. Set the DURABILITY option to SCHEMA_ONLY.

Correct answer: D

Explanation

The correct answer is D because setting the DURABILITY option to SCHEMA_ONLY allows the reference table to be memory-optimized and not persist data on disk, which speeds up access time and reduces overhead during server restarts. Option A is incorrect as it focuses on the target table rather than the reference table, which is crucial for the ETL process. Option B persists data which can slow down operations, and option C does not address the specific need for memory optimization of the reference table.