Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 80 discussion

Actual exam question from Microsoft's 70-762

Question #: 80
Topic #: 1

DRAG DROP -
You must create two staging database tables. The tables have the following requirements:

You need to select the correct storage mechanism for each table.
Which storage mechanism should you use? To answer, drag the appropriate table types to the correct tables. Each table type may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Select and Place:

Show Suggested Answer

Suggested Answer:

by Heisenberg008 at Feb. 24, 2020, 4:10 p.m.

Comments

Submit Cancel

Froze

Highly Voted 5 years, 2 months ago

I would answer: - disk-based table (100k is nothing, no need memory opimization, but because of CSV file is deleted immediately after load, you need to have something durable and this is the only solution) - memory-optimized non-durable table (1M records, loading every 5 minutes and CSV files are always available, so in case of failure you can easy reload data).

upvoted 32 times

MML

4 years, 11 months ago

I agree

upvoted 3 times

...

stm22

4 years, 11 months ago

agree...well said

upvoted 3 times

...

Heisenberg008

Highly Voted 5 years, 4 months ago

I think answer for table 2 should be Memory-optimized non-durable table

upvoted 10 times

rya

5 years, 4 months ago

why not memory-optimized table variable?

upvoted 2 times

JohnFan

5 years, 3 months ago

Because non-durable memory-optimized tables do not incur logging overhead, transactions writing to them run faster than write operations on durable tables. However, to optimize performance of durable memory-optimized tables, configure delayed durability at the database or transaction level. Just as with disk-based tables, delayed durability for a memory-optimized table reduces the frequency with which SQL Server flushes log records to disk and enables SQL Server to commit transactions before writing log records to disk.

upvoted 1 times

...

Anette

4 years, 12 months ago

I think you rae 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.

upvoted 1 times

Anette

4 years, 12 months ago

Since we have to do with staging database tables

upvoted 1 times

...

TheDUdeu

Most Recent 4 years, 5 months ago

Answer is correct for the second one a memory optimized table variable with schema and data you don't want to lose anything plus it is more efficient then a durable table

upvoted 1 times

...

Jay2

5 years ago

Did anyone notice table 2 loads almost 300 million rows each day? I'd rather not use my server's memory for just one staging table....

upvoted 1 times

Cococo

4 years, 9 months ago

Yep, "You need to select the correct storage mechanism for each table." - for each table

upvoted 1 times

...

JohnFan

5 years, 3 months ago

Temporary tables and table variables are different ways to fulfill this need. As an alternative, an application might use table-valued parameters to send multiple rows of data to a stored procedure or function and avoid the need for temporary tables or table variables. All three of these methods require writes to tempdb and potentially incur additional execution time due to the IO overhead.

upvoted 1 times

JohnFan

5 years, 3 months ago

You can instead use memory-optimized temporary tables, table variables, and table-valued parameters to take advantage of the same optimizations available for memory-optimized tables. By doing so, you can eliminate both the tempdb contention and the IO overhead. You can achieve faster execution time when using these objects in a natively compiled stored procedure.

upvoted 1 times

JohnFan

5 years, 3 months ago

A temp table can have indexes, whereas a table variable can only have a primary index. If speed is an issue Table variables can be faster, but obviously if there are a lot of records, or the need to search the temp table of a clustered index, then a Temp Table would be better. Temporary Tables are physically created in the tempdb database. These tables act as the normal table and also can have constraints, index like normal tables. Table Variable acts like a variable and exists for a particular batch of query execution. ... It is created in the memory database but may be pushed out to tempdb.

upvoted 2 times

Froze

5 years, 2 months ago

Man, you are putting a lot of information but not pointing the answer. Such behavior called SPAM.

upvoted 11 times

Nickname17

5 years, 2 months ago

Here is important information - A temp table can have indexes, whereas a table variable can only have a primary index.

upvoted 1 times

...

rcbrown

5 years, 3 months ago

i agree with heisenberg. it says csv must be available if reload required. non-durable will not keep data on power outage. however we can reload csv if needed

upvoted 4 times

JohnFan

5 years, 3 months ago

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.

upvoted 3 times

...