Microsoft 70-762 Online Practice Questions and Exam Preparation

Microsoft 70-762 Online Questions & Answers

• Question 141:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.

  You need to identify the indexes that are not being used so that you can remove them from the database.

  Solution: You run the sys.dm_db_index_physical_stats dynamic management view.

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

• Question 142:

  HOTSPOT

  You are performing a code review for Transact-SQL statements.

  Which action does the constraint in each statement perform? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

• Question 143:

  Note: The 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 question in the series. Information and details provided in a question apply only to that question.

  You have a database named DB1. The database does not have a memory optimized filegroup. You create a table by running the following Transact-SQL statement:

  

  The table is currently used for OLTP workloads. The analytics user group needs to perform real-time operational analytics that scan most of the records in the table to aggregate on a number of columns. You need to add the most efficient index to support the analytics workload without changing the OLTP application.

  What should you do?

  A. Create a clustered indexon the table.
  B. Create a nonclustered index on the table.
  C. Create a nonclustered filtered index on the table.
  D. Create a clustered columnstore index on the table.
  E. Create a nonclustered columnstore index on the table.
  F. Create a hash index on the table.
  E. Create a nonclustered columnstore index on the table.

  ---

  A nonclustered columnstore index enables real-time operational analytics in which the OLTP workload uses the underlying clustered index, while analytics run concurrently on the columnstore index.

  Columnstore indexes can achieve up to 100xbetter performance on analytics and data warehousing workloads and up to 10x better data compression than traditional rowstore indexes. These recommendations will help your queries achieve the very fast query performance that columnstore indexes are designed to provide.

  References:https://msdn.microsoft.com/en-us/library/gg492088.aspx

• Question 144:

  You have multiple queries that take a long time to complete.

  You need to identify the cause by using detailed information about the Transact-SQL statements in the queries. The Transact-SQL statements must not run as part of the analysis.

  Which Transact-SQL statement should you run?

  A. SET STATISTICS PROFILE OFF
  B. SET SHOWPLAN_TEXT OFF
  C. SET SHOWPLAN_ALL ON
  D. SET STATISTICS PROFILE ON
  C. SET SHOWPLAN_ALL ON

  ---

  SET SHOWPLAN_ALL ON causes Microsoft SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed and provides estimates of the resource requirements for the statements.

  Incorrect Answers:

  D: When STATISTICS PROFILE is ON, each executed query returns its regular result set, followed by an additional result set that shows a profile of the query execution. Displays the profile information for a statement. STATISTICS PROFILE works for ad hoc queries, views, and stored procedures.

  References: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-showplan-all-transact-sql

• Question 145:

  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 questions sets might have more than onecorrect 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 need to collect data from the following two sources:

  1.

  The performance counters of the operating system

  2.

  Microsoft SQL Server events

  You must analyze the two datasets side-by side by using a single tool.

  Solution: You use dynamic management views and Data Collector Sets (DCs) in Performance Monitor to collect performance data. You use SQL Server Management Studio (SSMS) to analyze the data.

  Does this meet the goal?

  A. Yes
  B. No
  B. No

  ---

  Reference: https://www.sqlshack.com/dba-guide-sql-server-performance-troubleshooting-part-2-monitoring-utilities/

• Question 146:

  DRAG DROP

  You need to build a function that meets the following requirements: Returns multiple rows Optimizes the performance of the query within the function

  How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL statements to the correct locations. Each Transact-SQL segment 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:

  

  

  ---

  References: https://technet.microsoft.com/en-us/library/ms189294(v=sql.105).aspx

• Question 147:

  DRAG DROP

  Your database contains a table named Invoices with a unique identifier column named InvoiceID.

  You have a batch update process that reads data from an input file and updates the table.

  You create a clustered columnstore index on the table and must update the configuration to meet the following requirements:

  1. The table must remain online.

  2. Perform analytic queries during business hours.

  3. Each row must have a unique key.

  You need to configure the index to optimize performance.

  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:

  

  

  ---

  Reference:

  https://solutioncenter.apexsql.com/why-when-and-how-to-rebuild-and-reorganize-sql-server-indexes/

  https://docs.microsoft.com/en-us/sql/database-engine/using-nonclustered-columnstore-indexes?view=sql-server-2014

• Question 148:

  Note: This question is part of a series of questions that present the same scenario. Each question in this series contains a unique solution. Determine whether the solution meets the stated goals. The Account table was created using the following Transact-SQL statement:

  

  There are more than 1 billion records in the Account table. The Account Number column uniquely identifies each account. The ProductCode column has 100 different values. The values are evenly distributed in the table. Table statistics are refreshed and up to date.

  You frequently run the following Transact-SQL SELECT statements:

  

  You must avoid table scans when you run the queries. You need to create one or more indexes for the table. Solution: You run the following Transact-SQL statement:

  CREATE CLUSTERED INDEX PK_Account ON Account(ProductCode);

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  We need an index on the productCode column as well. References:https://msdn.microsoft.com/en-us/library/ms190457.aspx

• Question 149:

  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 questions 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 a 3-TB database. The database server has 64 CPU cores.

  You plan to migrate the database to Microsoft Azure SQL Database.

  You need to select the service tier for the Azure SQL database. The solution must meet or exceed the current processing capacity.

  Solution: You select the Standard service tier.

  Does this meet the goal?

  A. Yes
  B. No
  B. No

  ---

  Premium service is required for 3 TB of storage. Single database DTU and storage limits

  

  References: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers-dtu

• Question 150:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.

  You have a database that contains a table named Employees. The table stored information about the employees of your company. You need to implement the following auditing rules for the Employees table:

  - Record any changes that are made to the data in the Employees table.

  -

  Customize the data recorded by the audit operations.

  Solution: You implement a stored procedure on the Employees table.

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  We should use table-valued functions, not procedures, to customize the recorded change data. References: https://msdn.microsoft.com/en-us/library/cc645858.aspx