Microsoft 70-762 Online Practice Questions and Exam Preparation

Microsoft 70-762 Online Questions & Answers

• Question 81:

  DRAG DROP

  You manage a database that includes the tables shown in the exhibit. (Click the Exhibit button.)

  You plan to create a DML trigger that reads the value of the LineTotal column for each row in the PurchaseOrderDetail table. The trigger must add the value obtained to the value in the SubTotal column of the PurchaseOrderHeader table.

  You need to organize the list to form the appropriate Transact-SQL statement.

  Which five Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

  Table diagram

  

  Select and Place:

  

  

  ---

  Box 1: Create TRIGGER NewPODetail

  Box 2: ON PurchaseDetail

  Box 3: AFTER INSERT AS

  Basic syntax:

  CREATE TRIGGER trigger_name

  ON { table | view }

  { FOR | AFTER | INSTEAD OF }

  { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }

  AS

  { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME }

  Box 4: Update PurchaseOrderHeader

  SET SubTotal = SubTotal + LineTotal

  FROM inserted

  Box 5: WHERE PurchaserOrderHeader.PurchaseOrderID=

  Inserted.PurchaseOrderID

  The trigger must read the value of the LineTotal column for each row in the PurchaseOrderDetail table. The trigger must add the value obtained to the value in the SubTotal column of the PurchaseOrderHeader table.

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

• Question 82:

  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
  B. CLR procedure

  ---

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

  Database users report that SELECT statements take a long time to return results. You run the following Transact-SQL statement:

  

  You need to create one nonclustered covering index that contains all of the columns in the above table. You must minimize index key size. Which Transact-SQL statement should you run?

  A. CREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserName);
  B. CREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserStatus) INCLUDE (UserName);
  C. CREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserStatus, UserName);
  D. CREATE NONCLUSTERED INDEX IX_User ON Users (UserStatus, CountryCode) INCLUDE (UserName);
  D. CREATE NONCLUSTERED INDEX IX_User ON Users (UserStatus, CountryCode) INCLUDE (UserName);

  ---

  Use the UserStatus as the first column in the index, as it is an in_equality column. Incorrect Answers:

  A: UserStatus is not included.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns

• Question 84:

  You are experiencing performance issues with the database server.

  You need to evaluate schema locking issues, plan cache memory pressure points, and backup I/O problems.

  What should you create?

  A. a System Monitor report
  B. a sys.dm_exec_query_stats dynamic management view query
  C. a sys.dm_exec_session_wait_stats dynamicmanagement view query
  D. an Activity Monitor session in Microsoft SQL Management Studio.
  C. a sys.dm_exec_session_wait_stats dynamicmanagement view query

  ---

  sys.dm_exec_session_wait_stats returns information about all the waits encountered by threads that executed for each session. You can use this view to diagnose performance issues with the SQL Server session and also with specific queries and batches.

  Note: SQL Server wait stats are, at their highest conceptual level, grouped into two broad categories: signal waits and resource waits. A signal wait is accumulated by processes running on SQL Server which are waiting for a CPU to become available (so called because the process has "signaled" that it is ready for processing). A resource wait is accumulated by processes running on SQL Server which are waiting fora specific resource to become available, such as waiting for the release of a lock on a specific record.

• Question 85:

  You have the following stored procedure:

  

  The Numbers table becomes unavailable when you run the stored procedure. The stored procedure obtains an exclusive lock on the table and does not release the lock.

  What are two possible ways to resolve the issue? Each correct answer presents a complete solution.

  NOTE: Each correct selection is worth one point.

  A. Remove the implicit transaction and the SET ANSI_DEFAULTS ON statement.
  B. Set the ANSI_DEFAULT statement to OFF and add a COMMIT TRANSACTION statement after the INSERT statement.
  C. Add a COMMIT TRANSACTION statement after the INSERT statement.
  D. Remove the SET ANSI_DEFAULTS ON statement.
  C. Add a COMMIT TRANSACTION statement after the INSERT statement.
  D. Remove the SET ANSI_DEFAULTS ON statement.

  ---

  SET ANSI_DEFAULTS is a server-side setting that the client does not modify. When enabled (ON), this option enables SET IMPLICIT_TRANSACTIONS (and some other options).

  The SET IMPLICIT_TRANSACTIONS, when ON, the system is in implicit transaction mode.

  This means that if @@TRANCOUNT = 0, any of the following Transact-SQL statements begins a new transaction. It is equivalent to an unseen BEGIN TRANSACTION being executed first: ALTER TABLE, FETCH, REVOKE, BEGIN

  TRANSACTION, GRANT, SELECT, CREATE, INSERT, TRUNCATE TABLE, DELETE, OPEN, UPDATE, DROP.

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

• Question 86:

  You manage a database that supports an Internet of Things (IoS) solution. The database records metrics from over 100 million devices every minute. The database requires 99.995% uptime.

  The database uses a table named Checkins that is 100 gigabytes (GB) in size. The Checkins table stores metrics from the devices. The database also has a table named Archive that stores four terabytes (TB) of data. You use stored

  procedures for all access to the tables.

  You observe that the wait type PAGELATCH_IO causes large amounts of blocking.

  You need to resolve the blocking issues while minimizing downtime for the database.

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

  A. Convert all stored procedures that access the Checkins table to natively compiled procedures.
  B. Convert the Checkins table to an In-Memory OLTP table.
  C. Convert all tables to clustered columnstore indexes.
  D. Convert the Checkins table to a clustered columnstore index.
  A. Convert all stored procedures that access the Checkins table to natively compiled procedures.
  B. Convert the Checkins table to an In-Memory OLTP table.

  ---

  Natively compiled stored procedures are Transact-SQL stored procedures compiled to native code that access memory-optimized tables. Natively compiled stored procedures allow for efficient execution of the queries and business logic in the stored procedure.

  SQL Server In-Memory OLTP helps improve performance of OLTP applications through efficient, memory-optimized data access, native compilation of business logic, and lock- and latch free algorithms. The In-Memory OLTP feature includes memory-optimized tables and table types, as well as native compilation of Transact-SQL stored procedures for efficient access to these tables.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/natively-compiled-stored-procedures

  https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/memory-optimized-tables

• Question 87:

  You have a database that users query frequently.

  The users report that during peak business hours, the queries take longer than expected to execute.

  A junior database administrator uses Microsoft SQL Server Profiler on the database server to trace the session activities.

  While performing the trace, the performance of the database server worsens, and the server crashes.

  You need to recommend a solution to collect the query run times. The solution must minimize the impact on the resources of the database server.

  What should you recommend?

  A. Increase the free space on the system drive of the database server, and then use SQL Server Profiler on the server to trace the session activities.
  B. Collect session activity data by using SQL Server Extended Events.
  C. Clean up tempdb, and then use SQL Server Profiler on the database server to trace the session activities.
  D. Collect performance data by using a Data Collector Set (DCS) in Performance Monitor.
  B. Collect session activity data by using SQL Server Extended Events.

  ---

  SQL Server Extended Events has a highly scalable and highly configurable architecture that allows users to collect as much or as little information as is necessary to troubleshoot or identify a performance problem. Extended Events is a light weight performance monitoring system that uses very few performance resources. Extended Events provides two graphical user interfaces (New Session Wizard and New Session) to create, modify, display, and analyze your session data.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events?view=sql-server-2017

• Question 88:

  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 create a stored procedure that

  updates the Customer, CustomerInfo, OrderHeader, and OrderDetails tables in order.

  You need to ensure that the stored procedure:

  Runs within a single transaction.

  Commits updates to the Customer and CustomerInfo tables regardless of the status of updates to the OrderHeader and OrderDetail tables.

  Commits changes to all four tables when updates to all four tables are successful.

  Solution: You create a stored procedure that includes the following Transact-SQL segment:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  All four tables are updated in a single transaction.

  Need to handle the case where the first two updates (OrderHeader, OrderDetail) are successful, but either the 3rd or the 4th (OrderHeader, OrderDetail) fail. Can add a variable in the BEGIN TRY block, and test the variable in the BEGIN CATCH block.

  References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql

• Question 89:

  You must create two staging database tables. The tables have the following requirements:

  

  You need to select the correct storage mechanism for each table.

  Which storage mechanism should you use? To answer, drag the appropriate table types to the correct tables. Each table type 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:

  

  

• Question 90:

  DRAG DROP

  Case study 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.

  

  You observe that the four indexes require a large amount of disk space. You must reduce the amount of disk space that the indexes are using.

  You need to create a single index to replace the existing indexes.

  Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

  NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

  Select and Place:

  

  

  ---

  Scenario: You observe that the four indexes require a large amount of disk space. You must reduce the amount of disk space that the indexes are using.