Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 176 discussion

Actual exam question from Microsoft's 70-762

Question #: 176
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 BEGIN TRANSACTION AND COMMIT OR ROLLBACK TRANSACTION statements for the connection
B. the SERIALIZABLE table hint on memory-optimized tables
C. the CONTAINS MEMORY_OPTIMIZED_DATA option for the file group
D. the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=OFF option for the database

Show Suggested Answer

Suggested Answer: A 🗳️
Your Transact-SQL contains the code BEGIN TRANSACTION, along with an eventual COMMIT TRANSACTION. Two or more statements can be corralled into the same transaction.
In explicit mode, you must either use the database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT or code a table hint about the transaction isolation level on the memory-optimized table in the FROM clause.
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 sainioz at Jan. 24, 2020, 1:21 p.m.

Comments

Submit Cancel

stm22

Highly Voted 4 years, 10 months ago

i passed 70-762 today. 43 of 46 questions on the exam were from this site...learn all the questions here!! the other 3 were about how to assign a database user so the user doesn;t have to use the schema name in queries thanks to all of you for the invaluable discussions!!!!!

upvoted 19 times

...

amar111

Highly Voted 5 years ago

Correct answer should be - set Memory_Optimized_Elevate_To_Snapshot ON . But as it is set to off in options available . So the best available option left is A.

upvoted 9 times

...

melvin9900

Most Recent 4 years, 10 months ago

Based on the below I think option B is correct. I may be missing something Cross-Container Transaction A transaction is called a cross-container transaction if it: Accesses a memory-optimized table from interpreted Transact-SQL; or Executes a native proc when a transaction is already open (XACT_STATE() = 1). The term "cross-container" derives from the fact that the transaction runs across the two transaction management containers, one for disk-based tables and one for memory-optimized tables. Within a single cross-container transaction, different isolation levels can be used for accessing disk-based and memory-optimized tables. This difference is expressed through explicit table hints such as WITH (SERIALIZABLE) or through the database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT, which implicitly elevates the isolation level for memory-optimized table to snapshot if the TRANSACTION ISOLATION LEVEL is configured as READ COMMITTED or READ UNCOMMITTED

upvoted 6 times

melvin9900

4 years, 10 months ago

-- Different isolation levels for -- disk-based tables versus memory-optimized tables, -- within one explicit transaction. SET TRANSACTION ISOLATION LEVEL READ COMMITTED; go BEGIN TRANSACTION; -- Table_D1 is a traditional disk-based table, accessed using READ COMMITTED isolation. SELECT * FROM Table_D1; -- Table_MO6 and Table_MO7 are memory-optimized tables. -- Table_MO7 is accessed using SERIALIZABLE isolation, -- while Table_MO6 does not have a specific isolation level. INSERT Table_MO6 SELECT * FROM Table_MO7 WITH (SERIALIZABLE); COMMIT TRANSACTION; go

upvoted 2 times

...

Anette

4 years, 11 months ago

This is the same with question 106

upvoted 2 times

...

Nickname17

5 years, 2 months ago

C is irrelevant. B is possible only if the disk is read committed, read uncommitted, or read committed snapshot; not for repeatable read and serializable; based on info. given, we cannot ensure it. For D, just opposite, you should turn it on.

upvoted 2 times

Nickname17

5 years ago

SQL server uses the file group container defined by Contains memory_optimized_data to store checkpoint files necessary for memory-optimized table.

upvoted 1 times

...

Bartek

5 years, 3 months ago

Think the right answer should be D Becouse assume transaction is working on default mode (Read Committed, autocommit) If we put MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = OFF and we receive errors from the procedure that means our transactions are not working on autocommit mode and we have to change MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT to ON or have to put in procedures new statements: -hints near all memory-optimized tables

upvoted 2 times

Bartek

5 years, 3 months ago

Of course if Your sttuborn then You may do it in another way. For example; 1. Transactions work on default transaction isolation levels and without any table hints and database changes (MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON ) 2. Then we assume that the transaction is working in READ COMMITTED isolation level and its Autocommit type of transaction 3. If Our procedures and transactions inside them looks like above then the only way (from A,B,C,D option) is that to add BEGIN/COMMIT and check that error occurred or not after procedures execution. 4.If error occurs then we know we have to establish table hints inside them or put setting MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON on all database becouse procedures are working in IMPLICIT or EXPLICIT transaction mode

upvoted 2 times

Bartek

5 years, 3 months ago

Then the correct answer can be "A" :P

upvoted 4 times

...

MML

4 years, 11 months ago

So the correct answer i s A

upvoted 2 times

...

JohnFan

5 years, 4 months ago

Supported Isolation Levels for Cross-Container Transactions There are limitations on the isolation levels used with operations on memory-optimized tables in cross-container transactions. Memory-optimized tables support the isolation levels SNAPSHOT, REPEATABLE READ, and SERIALIZABLE. For autocommit transactions, memory-optimized tables support the isolation level READ COMMITTED. The following scenarios are supported: READ UNCOMMITTED, READ COMMITTED, and READ_COMMITTED_SNAPSHOT cross-container transactions can access memory-optimized tables under SNAPSHOT, REPEATABLE READ, and SERIALIZABLE isolation. The READ COMMITTED guarantee holds for the transaction; all rows read by the transaction have been committed to the database. REPEATABLE READ and SERIALIZABLE transactions can access memory-optimized tables under SNAPSHOT isolation. https://docs.microsoft.com/en-us/sql/database-engine/cross-container-transactions?view=sql-server-2014

upvoted 1 times

...

JohnFan

5 years, 4 months ago

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.

upvoted 3 times

...

sainioz

5 years, 4 months ago

Correct answer D http://www.data-united.co.uk/tag/memory_optimized_elevate_to_snapshot/

upvoted 2 times

Anette

4 years, 11 months ago

But it must be ON and not OFF. The same questions but with other alternatives is in Question 106, and answer is : the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON option for the database

upvoted 3 times

...