Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 331:

  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 developing a Microsoft SQL Server Integration Services (SSIS) package.

  You need to use XPath to extract information from documents.

  Which SSIS Toolbox item should you use?

  A. CDC Control task
  B. CDC Splitter
  C. Union All
  D. XML task
  E. Fuzzy Grouping
  F. Merge
  G. Merge Join
  B. CDC Splitter

• Question 332:

  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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You have a data warehouse that stores information about products, sales, and orders for a manufacturing company. The instance contains a database that has two tables named SalesOrderHeader and SalesOrderDetail. SalesOrderHeader

  has 500,000 rows and SalesOrderDetail has 3,000,000 rows.

  Users report performance degradation when they run the following stored procedure:

  

  You need to optimize performance.

  Solution: You run the following Transact-SQL statement:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  Microsoft recommend against specifying 0 PERCENT or 0 ROWS in a CREATE STATISTICS..WITH SAMPLE statement. When 0 PERCENT or ROWS is specified, the statistics object is created but does not contain statistics data. References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-statistics-transact- sql

• Question 333:

  You manage Master Data Services (MDS).

  You need to create a new entity with the following requirements:

  

  Maximize the performance of the MDS system.

  

  Ensure that the Entity change logs are stored.

  You need to configure the Transaction Log Type setting. Which type should you use?

  A. Full
  B. None
  C. Attribute
  D. Member
  E. Simple
  D. Member

• Question 334:

  DRAG DROP

  You are developing a SQL Server Integration Services (SSIS) package.

  The package contains several tasks that must repeat until an expression evaluates to FALSE.

  You need to add and configure a container to enable this design.

  Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order,)

  Select and Place:

  

  

• Question 335:

  You are designing a SQL Server Integration Services (SSIS) solution. The solution will contain an SSIS project that includes several SSIS packages. Each SSIS package will define the same connection managers and variables.

  You have the following requirements:

  A. Use a project deployment model. Modify connection manager properties to use project parameters. Ensure that the SSISDB database is created.
  B. Use a project deployment model. Configure connections in an XML configuration file referenced by an environment variable that corresponds to the SQL Server environment of each SSIS package.
  C. Use a package deployment model. Use a SQL Server package configuration with a common filter. Change the contents of the SSIS Configurations table at runtime.
  D. Use a package deployment model. Save each SSIS package to a file share that can be accessed from all environments.

  A. Use a project deployment model. Modify connection manager properties to use project parameters. Ensure that the SSISDB database is created.

• Question 336:

  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 have a database named DB1.

  You need to track auditing data for four tables in DB1 by using change data capture.

  Which stored procedure should you execute first?

  A. catalog.deploy_project
  B. catalog.restore_project
  C. catalog.stop_operation
  D. sys.sp_cdc_add_job
  E. sys.sp_cdc_change_job
  F. sys.sp_cdc_disable_db
  D. sys.sp_cdc_add_job

  ---

  Because the cleanup and capture jobs are created by default, the sys.sp_cdc_add_job stored procedure is necessary only when a job has been explicitly dropped and must be recreated.

  Note: sys.sp_cdc_add_job creates a change data capture cleanup or capture job in the current database. A cleanup job is created using the default values when the first table in the database is enabled for change data capture. A capture job

  is created using the default values when the first table in the database is enabled for change data capture and no transactional publications exist for the database. When a transactional publication exists, the transactional log reader is used to

  drive the capture mechanism, and a separate capture job is neither required nor allowed.

  Note: sys.sp_cdc_change_job

  References: https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/track-data-changes-sql-server

• Question 337:

  DRAG DROP

  You are creating a sales data warehouse. When a product exists in the product dimension, you update the product name. When a product does not exist, you insert a new record.

  In the current implementation, the DimProduct table must be scanned twice, once for the insert and again for the update. As a result, inserts and updates to the DimProduct table take longer than expected.

  You need to create a solution that uses a single command to perform an update and an insert.

  How should you use a MERGE T-SQL statement to accomplish this goal? (To answer, drag the appropriate answer choice from the list of options to the correct location or locations in the answer area.

  You may need to drag the split bar between panes or scroll to view content.)

  Select and Place:

  

  

• Question 338:

  You maintain a SQL Server Integration Services (SSIS) package. The package was developed by using SQL Server 2008 Business Intelligence Development Studio (BIDS).

  The package includes custom scripts that must be upgraded. You need to upgrade the package to SQL Server 2016.

  Which tool should you use?

  A. SSIS Upgrade Wizard in SQL Server 2008 BIDS
  B. SSIS Upgrade Wizard in SQL Server Data Tools
  C. SQL Server DTExecUI utility (dtexecui.exe)
  D. SQL Server dtexec utility (dtexec.exe)
  B. SSIS Upgrade Wizard in SQL Server Data Tools

• Question 339:

  You are creating a SQL Server Master Data Services (MDS) model. This model is used to store a master list of products.

  An attribute must be added to the Product entity to define the sales manager responsible for each product.

  You need to create an attribute in the Product entity that prevents users from entering invalid sales manager values.

  Which type of attribute should you create?

  A. Recursive
  B. Explicit
  C. Domain-based
  D. User-defined
  E. Derived
  F. Parent
  C. Domain-based

  ---

  Explanation: References:

  http://msdn.microsoft.com/en-us/library/bb190163.aspx http://msdn.microsoft.com/en-us/library/ee633737.aspx http://msdn.microsoft.com/en-us/library/ee633759.aspx http://msdn.microsoft.com/en-us/library/ee633745.aspx http://

  msdn.microsoft.com/en-us/library/ee633724.aspx http://msdn.microsoft.com/en-us/library/ee633733.aspx http://msdn.microsoft.com/en-us/library/ff487058.aspx

• Question 340:

  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 are configuring a Microsoft SQL server named dw1for a new data warehouse. The server contains eight drives and eight processor cores. Each drive uses a separate physical disk.

  You need to configure storage for the tempdb database. The solution must minimize the amount of time it takes to process daily ETLjobs.

  Solution: You configure eight files for the tempdb database. You place the files on a drive that will NOT store the user database files.

  Does this meet the goal?

  A. Yes
  B. No
  B. No