Exam 70-762 topic 1 question 106 discussion

Actual exam question from Microsoft's 70-762

Question #: 106
Topic #: 1

You are creating the following two stored procedures:
✑ A natively-compiled stored procedure
✑ An interpreted stored procedure that accesses both disk-based and memory-optimized tables
Both stored procedures run within transactions.
You need to ensure that cross-container transactions are possible.
Which setting or option should you use?

A. the SET TRANSACTION_READ_COMMITTED isolation level for the connection
B. the SERIALIZABLE table hint on disk-based tables
C. the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON option for the database
D. the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=OFF option for the database

Show Suggested Answer

Suggested Answer: C 🗳️
Provide a supported isolation level for the memory-optimized table using a table hint, such as WITH (SNAPSHOT). The need for the WITH (SNAPSHOT) hint can be avoided through the use of the database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT. When this option is set to ON, access to a memory- optimized table under a lower isolation level is automatically elevated to SNAPSHOT isolation.
Incorrect Answers:
B: Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/transactions-with-memory-optimized-tables?view=sql-server-2017

by Anette at July 8, 2020, 5:11 p.m.

Comments

Submit Cancel

stm22

Highly Voted 4 years, 11 months ago

C seems best answer according to https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/transactions-with-memory-optimized-tables?view=sql-server-ver15

upvoted 8 times

...

Anette

Most Recent 4 years, 11 months ago

It loks to me like a, b, c are all right. Of course they cannot be all right but... Memory-optimized tables support the isolation levels SNAPSHOT, REPEATABLE READ, and SERIALIZABLE. For autocommit transactions, memory-optimized tables support the isolation level READ COMMITTED. Can anyone give some better explanation why its C, or whatever?

upvoted 1 times

databasejamdown

4 years, 7 months ago

I think b is wrong because it specifies that serializable should be on the disk based table when the hint should be on the memory optimized table in cross-container transactions

upvoted 3 times

...

databasejamdown

4 years, 7 months ago

I think A is wrong because read commited only works for the default auto commit statement level transactions. The Question states that both procedures runs within transactions which implies either implicit or explicit. In either case, read commited is not supported for memory optimized tables. This is actually explained in the wrong answer section. However, they said B instead of A in the wrong answer section

upvoted 3 times

...