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?
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.
Of all of the options, D is the only option that can be implemented on its own, AND takes the least amount of time to populate the reference table.
Option A can’t work because a natively compiled SP needs all the tables to be memory optimized.
Option B will work but it takes a little more time to populate the reference table compared to D
Option C can’t work because the tables need to be memory optimized and this answer does not mention changing the tables.
This is strange answer. The server that hosts the table restarts daily. and we still create IN MEMORY TABLE.