Microsoft 70-762 Online Practice Questions and Exam Preparation

Microsoft 70-762 Online Questions & Answers

• Question 41:

  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_operational_stats dynamic management view.

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

• Question 42:

  Your company runs end-of-the-month accounting reports. While the reports run, other financial records are updated in the database.

  Users report that the reports take longer than expected to run.

  You need to reduce the amount of time it takes for the reports to run. The reports must show committed data only.

  What should you do?

  A. Use the NOLOCK option.
  B. Execute the DBCC UPDATEUSAGE statement.
  C. Use the max worker threads option.
  D. Use a table-valued parameter.
  E. Set SET ALLOW_SNAPSHOT_ISOLATION to ON.
  F. Set SET XACT_ABORT to ON.
  G. Execute the ALTER TABLE T1 SET (LOCK_ESCALATION = AUTO); statement.
  H. Use the OUTPUT parameters.
  E. Set SET ALLOW_SNAPSHOT_ISOLATION to ON.

  ---

  Snapshot isolation enhances concurrency for OLTP applications.

  Once snapshot isolation is enabled, updated row versions for each transaction are maintained in tempdb. A unique transaction sequence number identifies each transaction, and these unique numbers are recorded for each row version. The transaction works with the most recent row versions having a sequence number before the sequence number of the transaction. Newer row versions created after the transaction has begun are ignored by the transaction. References: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

• Question 43:

  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 table that has a clustered index and a nonclustered index. The indexes use different columns from the table. You have a query named Query1 that uses the nonclustered index. Users report that Query1 takes a long time to report results. You run Query1 and review the following statistics for an index seek operation:

  

  You need to resolve the performance issue. Solution: You defragment both indexes. Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  We see Actual Number of Row is 3571454, while Estimated Number of Rows is 0. This indicates that the statistics are old, and need to be updated.

• Question 44:

  You need to build a function that meets the following requirements:

  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 45:

  Note: This question is part of a series of questions that use the same or similar answer choices. As 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 Microsoft SQL Server database named DB1 that contains the following tables:

  

  You frequently run the following queries:

  

  There are no foreign key relationships between TBL1 and TBL2.

  You need to minimize the amount of time required for the two queries to return records from the tables.

  What should you do?

  A. Create clustered indexes on TBL1 and TBL2.
  B. Create a clustered index on TBL1.Create a nonclustered index on TBL2 and add the most frequently queried column as included columns.
  C. Create a nonclustered index on TBL2 only.
  D. Create UNIQUE constraints on both TBL1 and TBL2. Create a partitioned view that combines columns from TBL1 and TBL2.
  E. Drop existing indexes on TBL1 and then create a clustered columnstore index. Create a nonclustered columnstore index on TBL1.Create a nonclustered index on TBL2.
  F. Drop existing indexes on TBL1 and then create a clustered columnstore index. Create a nonclustered columnstore index on TBL1.Make no changes to TBL2.
  G. Create CHECK constraints on both TBL1 and TBL2. Create a partitioned view that combines columns from TBL1 and TBL2.
  H. Create an indexed view that combines columns from TBL1 and TBL2.
  B. Create a clustered index on TBL1.Create a nonclustered index on TBL2 and add the most frequently queried column as included columns.

• Question 46:

  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 Basic 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 47:

  HOTSPOT

  You use Query Store to optimize a query in a database.

  The query has two execution plans:

  

  Plan 2 is shown in the Plan 2 Execution Plan exhibit. (Click the Exhibit button.)

  

  Plan 10 is shown in the Plan 10 Execution Plan exhibit. (Click the Exhibit button.)

  You create an index at 22:24 based on the missing index suggestion in Plan 2.

  The average duration statistics for the query is shown in the Tracked Queries exhibit. (Click the Exhibit button.)

  You need to analyze the operators in the two execution plans.

  For each of the following statements, select Yes if the statement is true. Otherwise, select No.

  Plan 2 Execution Plan

  

  Plan 10 Execution Plan

  

  Tracked Queries

  

  Hot Area:

  

  

• Question 48:

  HOTSPOT

  This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to complete each case. However, there may be additional case studies and sections on this exam. You must manage your time to ensure that you are able to complete all questions included on this exam in the time provided.

  To answer the questions included in a case study, you will need to reference information that is provided in the case study. Case studies might contain exhibits and other resources that provide more information about the scenario that is described in the case study. Each question is independent of the other questions in this case study.

  At the end of this case study, a review scree will appear. This screen allows you to review your answers and to make changes before you move to the next section of the exam. After you begin a new section, you cannot return to this section.

  To start the case study

  To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore the content of the case study before you answer the questions. Clicking these buttons displays information such as business requirements. Existing environment, and problem statements. If the case study has an All Information tab, note that the information displayed is identical to the information displayed on the subsequent tabs. When you are ready to answer a question, click the Question button to return to the question.

  Background

  You have a database named Sales.

  The Customer table includes a column that stores the date for the last order that the customer placed.

  You plan to create a table named Leads. The Leads table is expected to contain approximately 20,000 records. Storage requirements for the Leads table must be minimized.

  Tables

  

  You need to implement a stored procedure that deletes a discontinued product from the Products table. You identify the following requirements:

  If an open order includes a discontinued product, the records for the product must not be deleted.

  The stored procedure must display a custom error message if a product record cannot be deleted. The message must identify the OrderID for the open order.

  What should you do? To answer, select the appropriate Transact-SQL segments in the answer area.

  Hot Area:

  

  

  ---

  Box 1: Try/Catch

  A TRY...CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection.

  Box 2: ERROR_MESSAGE()

  ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

  References:

  https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql

• Question 49:

  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.

  Your company has employees in different regions around the world.

  You need to create a database table that stores the following employee attendance information:

  1.

  Employee ID

  2.

  Date and time employee checked in to work

  3.

  Date and time employee checked out of work

  Date and time information must be time zone aware and must not store fractional seconds.

  Solution: You run the following Transact-SQL statement:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  Datetime2 stores fractional seconds.

  Datetime2 defines a date that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and

  optional user-specified precision.

  Reference:

  https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-2017

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

• Question 50:

  You have the following stored procedure that is called by other stored procedures and applications:

  

  You need to modify the stored procedure to meet the following requirements:

  Always return a value to the caller.

  Return 0 if @Status is NULL.

  Callers must be able to use @Status as a variable.

  Which two actions should you perform? Each correct answer presents part of the solution.

  NOTE: Each correct selection is worth one point.

  A. Replace NULL values with 0. Add a PRINT statement to return @Status.
  B. Add a RETURN statement.
  C. Replace NULL values with 0. Add an output parameter to return @Status.
  D. Replace NULL values with 0. Add a SELECT statement to return @Status.
  E. Add a PRINT statement.
  F. Add a SELECT statement to return @Status.
  G. Add an output parameter to return @Status.
  B. Add a RETURN statement.
  C. Replace NULL values with 0. Add an output parameter to return @Status.

  ---

  There are three ways of returning data from a procedure to a calling program: result sets, output parameters, and return codes.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure?view=sql-server-2017