Developing SQL Databases — Question 121

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?

Answer options

• 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

Correct answer: C

Explanation

The correct answer is C because enabling the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON option allows for cross-container transactions between natively compiled and interpreted stored procedures. The other options do not facilitate this requirement; for instance, setting the isolation level or using table hints does not address the cross-container transaction aspect.