Free Microsoft 070-767 Exam Dumps Questions & Answers

You have a database Ihat includes a table named dbo.sales. The table contains two billion rows. You created the table by running the following Transact-SQL statement:


You run the following queries against the dbo.sales table. All of the queries perform poorly.


The ETL process that populates the table uses bulk insert to load 10 million rows each day. The process currently takes six hours to load the records.
The value of the Refund column is equal to 1 for only 0.01 percent of the rows in the table. For all other rows, the value of the Refund column is equal to 0.
You need to maximize the performance of queries and the ETL process.
Which index type should you use for each query? To answer, select the appropriate index types in the answer area.
NOTE: Each correct selection is worth one point.

Correct Answer:

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are a database administrator for an e-commerce company that runs an online store. The company has the databases described in the following table.

Product prices are updated and are stored in a table named Products on DB1. The Products table is deleted and refreshed each night from MDS by using a Microsoft SQL Server Integration Services (SSIS) package. None of the data sources are sorted.
You need to update the SSIS package to add current prices to the Products table.
What should you use?

• A.  Sequential container
• B.  Balanced Data Distributor transformation
• C.  Merge transformation
• D.  MERGE statement
• E.  Union All transformation
• F.  Lookup transformation
• G.  Foreach Loop container
• H.  Merge Join transformation

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have an on-premises Microsoft SQL Server instance and a Microsoft Azure SQL Data Warehouse instance. You move data from the on-premises database to the data warehouse once each day by using a SQL Server Integration Services (SSIS) package.
You observe that the package no longer completes within the allotted time.
You need to determine which tasks are taking a long time to complete.
Solution: You alter the package to log the start and completion times for a task to a table in the on-premises SQL Server instance.
Does the solution meet the goal?

• A.  Yes
• B.  No

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are implementing a Microsoft SQL Server data warehouse with a multi-dimensional data model.
When testing a pilot version of the data warehouse, business users observe that the number of products in stock is inaccurate. The number of products in stock always increases and represents the total number of products that have ever been in stock.
You need to correct the existing model and ensure that it reflects the number of in-stock products. You must not change the overall structure of the data model.
What should you do?

• A.  dimension table reference relationship
• B.  non-additive measure
• C.  star schema
• D.  semi-additive measure

You have a Microsoft SQL Server Integration Services (SSIS) package that includes the control flow shown in the following diagram.

You need to choose the enumerator for the Foreach Loop container.
Which enumerator should you use?

• A.  Foreach NodeList Enumerator
• B.  Foreach Azure Blob Enumerator
• C.  Foreach ADO Enumerator
• D.  Foreach SMO Enumerator