Microsoft Microsoft Certifications 70-762 Questions & Answers

• Question 61:

  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 the series.

  Information and details provided in a question apply only to that question.

  You are developing an application to track customer sales.

  You need to create a database object that meets the following requirements:

  Launch when table data is modified.

  Evaluate the state a table before and after a data modification and take action based on the difference.

  Prevent malicious or incorrect table data operations.

  Prevent changes that violate referential integrity by cancelling the attempted data modification.

  Run managed code packaged in an assembly that is created in the Microsoft.NET Framework and located into Microsoft SQL Server.

  What should you create?

  A. extended procedure

  B. CLR procedure

  C. user-defined procedure

  D. DDL trigger

  E. scalar-valued function

  F. table-valued function

  Correct Answer: B

  You can create a database object inside SQL Server that is programmed in an assembly created in the Microsoft .NET Framework common language runtime (CLR). Database objects that can leverage the rich programming model provided

  by the CLR include DML triggers, DDL triggers, stored procedures, functions, aggregate functions, and types.

  Creating a CLR trigger (DML or DDL) in SQL Server involves the following steps:

  Define the trigger as a class in a .NETFramework-supported language. For more information about how to program triggers in the CLR, see CLR Triggers. Then, compile the class to build an assembly in the .NET Framework using the

  appropriate language compiler.

  Register the assembly in SQL Server using the CREATE ASSEMBLY statement. For more information about assemblies in SQL Server, see Assemblies (Database Engine).

  Create the trigger that references the registered assembly.

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

• Question 62:

  You use Microsoft SQL Server Profile to evaluate a query named Query1. The Profiler report indicates the following issues:

  At each level of the query plan, a low total number of rows are processed.

  The query uses many operations. This results in a high overall cost for the query.

  You need to identify the information that will be useful for the optimizer.

  What should you do?

  A. Start a SQL Server Profiler trace for the event class Performance statistics in the Performance event category.

  B. Create one Extended Events session with the sqlserver.missing_column_statistics event added.

  C. Start a SQL Server Profiler trace for the event class Soft Warnings in the Errors and Warnings event category.

  D. Create one Extended Events session with the sqlserver.error_reported event added.

  Correct Answer: A

  The Performance Statistics event class can be used to monitor the performance of queries, stored procedures, and triggers that are executing. Each of the six event subclasses indicates an event in the lifetime of queries, stored procedures, and triggers within the system. Using the combination of these event subclasses and the associated sys.dm_exec_query_stats, sys.dm_exec_procedure_stats and sys.dm_exec_trigger_stats dynamic management views, you can reconstitute the performance history of any given query, stored procedure, or trigger.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/performance-statistics-event-class?view=sql-server-2017

• Question 63:

  Background

  You have a database named HR1 that includes a table named Employee.

  You have several read-only, historical reports that contain regularly changing totals. The reports use multiple queries to estimate payroll expenses. The queries run concurrently. Users report that the payroll estimate reports do not always run. You must monitor the database to identify issues that prevent the reports from running.

  You plan to deploy the application to a database server that supports other applications. You must minimize the amount of storage that the database requires.

  Employee Table

  You use the following Transact-SQL statements to create, configure, and populate the Employee table:

  

  Application

  You have an application that updates the Employees table. The application calls the following stored procedures simultaneously and asynchronously:

  UspA: This stored procedure updates only the EmployeeStatus column.

  UspB: This stored procedure updates only the EmployeePayRate column.

  The application uses views to control access to data. Views must meet the following requirements:

  Allow user access to all columns in the tables that the view accesses.

  Restrict updates to only the rows that the view returns.

  Exhibit

  

  You are analyzing the performance of the database environment. You discover that locks that are held for a long period of time as the reports are generated.

  You need to generate the reports more quickly. The database must not use additional resources.

  What should you do?

  A. Update all FROM clauses of the DML statements to use the IGNORE_CONSTRAINTS table hint.

  B. Modify the report queries to use the UNION statement to combine the results of two or more queries.

  C. Apply a nonclustered index to all tables used in the report queries.

  D. Update the transaction level of the report query session to READ UNCOMMITTED.

  Correct Answer: D

  Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. This is the least restrictive of the isolation levels. References: https://technet.microsoft.com/en-us/library/ms173763(v=sql.105).aspx

• Question 64:

  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 database that is 130 GB and contains 500 million rows of data.

  Granular transactions and mass batch data imports change the database frequently throughout the day. Microsoft SQL Server Reporting Services (SSRS) uses the database to generate various reports by using several filters.

  You discover that some reports time out before they complete.

  You need to reduce the likelihood that the reports will time out.

  Solution: You increase the number of log files for the database. You store the log files across multiple disks.

  Does this meet the goal?

  A. Yes

  B. No

  Correct Answer: B

  Instead, create a file group for the indexes and a file group for the data files.

• Question 65:

  Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.

  You have a database that contains the following tables: BlogCategory, BlogEntry, ProductReview, Product, and SalesPerson. The tables were created using the following Transact SQL statements:

  

  You must modify the ProductReview Table to meet the following requirements:

  The table must reference the ProductID column in the Product table

  Existing records in the ProductReview table must not be validated with the Product table.

  Deleting records in the Product table must not be allowed if records are referenced by the ProductReview table.

  Changes to records in the Product table must propagate to the ProductReview table.

  You also have the following database tables: Order, ProductTypes, and SalesHistory. The transact-SQL statements for these tables are not available.

  You must modify the Orders table to meet the following requirements:

  Create new rows in the table without granting INSERT permissions to the table.

  Notify the sales person who places an order whether or not the order was completed.

  You must add the following constraints to the SalesHistory table:

  a constraint on the SaleID column that allows the field to be used as a record identifier

  a constant that uses the ProductID column to reference the Product column of the ProductTypes table

  a constraint on the CategoryID column that allows one row with a null value in the column

  a constraint that limits the SalePrice column to values greater than four

  Finance department users must be able to retrieve data from the SalesHistory table for sales persons where the value of the SalesYTD column is above a certain threshold.

  You plan to create a memory-optimized table named SalesOrder. The table must meet the following requirements:

  The table must hold 10 million unique sales orders.

  The table must use checkpoints to minimize I/O operations and must not use transaction logging.

  Data loss is acceptable.

  Performance for queries against the SalesOrder table that use WHERE clauses with exact equality operations must be optimized.

  You need to modify the environment to meet the requirements for the Orders table.

  What should you create?

  A. an AFTER UPDATE trigger

  B. a user-defined function

  C. a stored procedure with output parameters

  D. an INSTEAD OF INSERT trigger

  Correct Answer: D

  From Question: You must modify the Orders table to meet the following requirements: Create new rows in the table without granting INSERT permissions to the table. Notify the sales person who places an order whether or not the order was completed.

  References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017

• Question 66:

  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 rebuild the clustered index. Does the solution meet the goal?

  A. Yes

  B. No

  Correct Answer: B

  The query uses the nonclustered index, so improving the clustered index will not help. We should update statistics for the nonclustered index.

• Question 67:

  You need to create a view that can be indexed. You write the following statement.

  

  What should you add at line 02?

  A. with check_option

  B. with recompile

  C. with view_metadata

  D. with schemabinding

  Correct Answer: D

  The following steps are required to create an indexed view and are critical to the successful implementation of the indexed view:

  1.

  Verify the SET options are correct for all existing tables that will be referenced in the view.

  2.

  Verify that the SET options for the session are set correctly before you create any tables and the view.

  3.

  Verify that the view definition is deterministic.

  4.

  Create the view by using the WITH SCHEMABINDING option.

  5.

  Create the unique clustered index on the view.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-2017

• Question 68:

  You have a nonpartitioned table that has a single dimension. The table is named dim.Products.Projections.

  The table is queried frequently by several line-of-business applications. The data is updated frequently throughout the day by two processes.

  Users report that when they query data from dim.Products.Projections, the responses are slower than expected. The issue occurs when a large number of rows are being updated.

  You need to prevent the updates from slowing down the queries.

  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.

  Correct Answer: A

  The NOLOCK hint allows SQL to read data from tables by ignoring any locks and therefore not being blocked by other processes. This can improve query performance, but also introduces the possibility of dirty reads.

  References: https://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/

• Question 69:

  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.

  Correct Answer: E

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

  You have several real-time applications that constantly update data in a database. The applications run more than 400 transactions per second that insert and update new metrics from sensors.

  A new web dashboard is released to present the data from the sensors. Engineers report that the applications take longer than expected to commit updates.

  You need to change the dashboard queries to improve concurrency and to support reading uncommitted data.

  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.

  Correct Answer: A

  The NOLOCK hint allows SQL to read data from tables by ignoring any locks and therefore not being blocked by other processes. This can improve query performance, but also introduces the possibility of dirty reads.

  Incorrect Answers:

  F: When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

  G: DISABLE, not AUTO, would be better.

  There are two more lock escalation modes: AUTO and DISABLE.

  The AUTO mode enables lock escalation for partitioned tables only for the locked partition. For non-partitioned tables it works like TABLE. The DISABLE mode removes the lock escalation capability for the table and that is important when

  concurrency issues are more important than memory needs for specific tables.

  Note: SQL Server's locking mechanism uses memory resources to maintain locks. In situations where the number of row or page locks increases to a level that decreases the server's memory resources to a minimal level, SQL Server's locking strategy converts these locks to entire table locks, thus freeing memory held by the many single row or page locks to one table lock. This process is called lock escalation, which frees memory, but reduces table concurrency.

  References: https://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/