Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 127 discussion

Actual exam question from Microsoft's 70-762

Question #: 127
Topic #: 1

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.

Show Suggested Answer

Suggested Answer: D 🗳️
If you use temporary tables, table variables, or table-valued parameters, consider conversions of them to leverage memory-optimized tables and table variables to improve performance.
In-Memory OLTP provides the following objects that can be used for memory-optimizing temp tables and table variables:
✑ Memory-optimized tables

Durability = SCHEMA_ONLY -
✑ Memory-optimized table variables
Must be declared in two steps (rather than inline):
CREATE TYPE my_type AS TABLE ...; , then
DECLARE @mytablevariable my_type;.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/faster-temp-table-and-table-variable-by-using-memory-optimization? view=sql-server-2017

by Kuratko at Feb. 20, 2020, 4:29 p.m.

Comments

Submit Cancel

Anette

Highly Voted 4 years, 12 months ago

Its correct Non-durable By choosing this type of durability, you instruct SQL Server to persist only the table schema, but not the data. This option is most appropriate for use cases in which data is transient, such as an application’s session state management, or ETL staging. SQL Server never writes a non-durable table’s data changes to the transaction log. To define a non-durable table, use the SCHEMA_ONLY durability option

upvoted 13 times

...

Kuratko

Highly Voted 5 years, 4 months ago

"B" -> "The server that hosts the table restarts daily" You need to populate data ASAP after restart. "Set the DURABILITY option to SCHEMA_AND_DATA" will populate data to memory after easch restart, so you minimalize the time? Right?

upvoted 7 times

als2kool

5 years, 3 months ago

Non-durable memory optimized tables require no I/O so they are faster. So to minimize the time the answer is correct.

upvoted 4 times

JohnFan

5 years, 3 months ago

To define a non-durable table, use the SCHEMA_ONLY durability option

upvoted 3 times

...

Barbedx

Most Recent 4 years, 5 months ago

Answer B is correct. Not D because we can populate data in memory table in any time from SOURCE. So we don't lost this data if server restarts.

upvoted 1 times

Barbedx

4 years, 5 months ago

sorry, vice versa, not B but B, we don't need Shema_and_data, only SCHEMA

upvoted 1 times

Barbedx

4 years, 5 months ago

D!!!!!!

upvoted 1 times

...

Cococo

4 years, 10 months ago

Keep in mind they ALWAYS leave the key words. SCHEMA_ONLY When SQL Server is RESTARTED or a reconfiguration occurs in an Azure SQL Database, the table schema persists, but data in the table is lost. (This is unlike a table in tempdb, where both the table and its data are lost upon restart.) A typical scenario for creating a non-durable table is to store transient data, such as a STAGING table for an ETL process. A SCHEMA_ONLY durability avoids both transaction logging and checkpoint, which can significantly reduce I/O operations. https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/defining-durability-for-memory-optimized-objects?view=sql-server-ver15#:~:text=A%20SCHEMA_ONLY%20durability%20avoids%20both,significantly%20reduce%20I%2FO%20operations.&text=When%20you%20commit%20a%20fully,memory%2Doptimized%20table%20are%20permanent.

upvoted 1 times

...

melvin9900

4 years, 10 months ago

In the exam the option was "Convert the Target table to a memory-optimized table. Set the DURABILITY option to SCHEMA_ONLY." So I selected the option " Convert the reference table to a memory-optimized table. Set the DURABILITY option to SCHEMA_AND_DATA"

upvoted 4 times

lh2607

4 years, 7 months ago

You selected the wrong answer then.

upvoted 1 times

...

Hoglet

5 years ago

B or D, depending on the volatility of the reference table. If it's changing every day, then D. You don't gain anything from having stale data hanging around If it's static / changing weekly then it's B, you don't have to waste time populating it in the morning.

upvoted 3 times

...

amar111

5 years, 1 month ago

question says to reduce time to load reference data . So durability should be schema and data

upvoted 1 times

...