Microsoft 70-762 Online Practice Questions and Exam Preparation

Microsoft 70-762 Online Questions & Answers

• Question 11:

  You are creating the following two stored procedures:

  A natively-compiled stored procedure

  An interpreted stored procedure that accesses both disk-based and memory-optimized tables

  Both stored procedures run within transactions.

  You need to ensure that cross-container transactions are possible.

  Which setting or option should you use?

  A. the BEGIN TRANSACTION AND COMMIT OR ROLLBACK TRANSACTION statements for the connection
  B. the SERIALIZABLE table hint on memory-optimized tables
  C. the CONTAINS MEMORY_OPTIMIZED_DATA option for the file group
  D. the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=OFF option for the database
  A. the BEGIN TRANSACTION AND COMMIT OR ROLLBACK TRANSACTION statements for the connection

  ---

  Your Transact-SQL contains the code BEGIN TRANSACTION, along with an eventual COMMIT TRANSACTION. Two or more statements can be corralled into the same transaction.

  In explicit mode, you must either use the database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT or code a table hint about the transaction isolation level on the memory-optimized table in the FROM clause.

  Incorrect Answers:

  B: Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/transactions-with-memory-optimized-tables?view=sql-server-2017

• Question 12:

  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:

  -

  Return a value of 0 if data inserted successfully into the Customers table.

  -

  Return a value of 1 if data is not inserted successfully into the Customers table.

  -Support logic that is written by using managed code.

  What should you create?

  A. extended procedure
  B. CLR procedure
  C. user-defined procedure
  D. DML trigger
  E. DDL trigger
  F. scalar-valued function G. table-valued function
  B. CLR procedure

  ---

  DML triggers is a special type of stored procedure that automatically takes effect when a data manipulation language (DML) event takes place that affects the table or view defined in the trigger. DML events include INSERT, UPDATE, or DELETE statements.DML triggers can be used to enforce business rules and data integrity, query other tables, and include complex Transact-SQL statements.

  A CLR trigger is a type of DDL trigger. A CLR Trigger can be either an AFTER or INSTEAD OF trigger. A CLR trigger canalso be a DDL trigger. Instead of executing a Transact-SQL stored procedure, a CLR trigger executes one or more methods written in managed code that are members of an assembly created in the .NET Framework and uploaded in SQL Server.

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

• Question 13:

  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.
  A. Start a SQL Server Profiler trace for the event class Performance statistics in the Performance event category.

  ---

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

  You are analyzing the performance of a database environment.

  You suspect there are several missing indexes in the current database.

  You need to return a prioritized list of the missing indexes on the current database.

  How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments 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:

  

  

  ---

  Box 1: sys.db_db_missing_index_group_stats

  The sys.db_db_missing_index_group_stats table include the required columns for the main query: avg_total_user_cost, avg_user_impact, user_seeks, and user scans.

  Box 2: group_handle

  Example: The following query determines which missing indexes comprise a particular missing index group, and displays their column details. For the sake of this example, the missing index group handle is 24.

  SELECT migs.group_handle, mid.*

  FROM sys.dm_db_missing_index_group_stats AS migs

  INNER JOIN sys.dm_db_missing_index_groups AS mig

  ON (migs.group_handle = mig.index_group_handle)

  INNER JOIN sys.dm_db_missing_index_details AS mid

  ON (mig.index_handle = mid.index_handle)

  WHERE migs.group_handle = 24;

  Box 3: sys.db_db_missing_index_group_stats

  The sys.db_db_missing_index_group_stats table include the required columns for the subquery: avg_total_user_cost and avg_user_impact.

  Example: Find the 10 missing indexes with the highest anticipated improvement for user queries

  The following query determines which 10 missing indexes would produce the highest anticipated cumulative improvement, in descending order, for user queries.

  SELECT TOP 10 *

  FROM sys.dm_db_missing_index_group_stats

  ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC;

• Question 15:

  You have multiple stored procedures inside a transaction.

  You need to ensure that all the data modified by the transaction is rolled back if a stored procedure causes a deadlock or times out.

  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.
  F. Set SET XACT_ABORT to ON.

  ---

  When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. References: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-2017

• Question 16:

  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 reporting database that includes a non-partitioned fact table named Fact_Sales. The table is persisted on disk.

  Users report that their queries take a long time to complete. The system administrator reports that the table takes too much space in the database. You observe that there are no indexes defined on the table, and many columns have

  repeating values.

  You need to create the most efficient index on the table, minimize disk storage and improve reporting query performance.

  What should you do?

  A. Create a clustered index on 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.
  D. Create a clustered columnstore index on the table.

  ---

  The columnstore index is the standard for storing and querying largedata warehousing fact tables. It uses column-based data storage and query processing to achieve up to 10x query performance gains in your data warehouse over

  traditional row-oriented storage, and up to 10x data compression over the uncompressed data size.

  A clustered columnstore index is the physical storage for the entire table.

• Question 17:

  HOTSPOT

  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 named Sales that contains the following database tables: Customer, Order, and Products. The Products table and the Order table are shown in the following diagram.

  

  The customer table includes a column that stores the data 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.

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

  

  

  ---

  Using TRY...CATCH in Transact-SQL

  Errors in Transact-SQL code can be processed by using a TRY...CATCH construct.

  TRY...CATCH can use the following error function to capture error information:

  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://technet.microsoft.com/en-us/library/ms179296(v=sql.105).aspx

• Question 18:

  HOTSPOT

  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 named Sales that contains the following database tables: Customer, Order, and Products. The Products table and the Order table are shown in the following diagram.

  

  The customer table includes a column that stores the data 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.

  You need to modify the database design to meet the following requirements:

  Rows in the Orders table must always have a valid value for the ProductID column.

  Rows in the Products table must not be deleted if they arepart of any rows in the Orders table.

  All rows in both tables must be unique.

  In the table below, identify the constraint that must be configured for each table. NOTE: Make only one selection in each column.

  Hot Area:

  

  

  ---

  A FOREIGN KEY in one table points to a PRIMARY KEY in another table. Here the foreign key constraint is put on the ProductID in the Orders, and points to the ProductID of the Products table. With a check constraint on the ProductID we can ensure that the Products table contains only unique rows.

  References:http://www.w3schools.com/sql/sql_foreignkey.asp

• Question 19:

  DRAG DROP

  You create tables by using the following Transact-SQL statements:

  

  Each customer may have multiple addresses but only one is the primary address.

  You must plan a solution that meets the following requirements:

  Return both customers and address information.

  Return only the primary address of the customer.

  Allow updates of all customer information and address details with the exception of the identity columns and the IsActive column.

  Which three actions should you perform is 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:

  

  

  ---

  Step 1:

  SQL Server Views WITH CHECK OPTION. Views can be created in SQL Server WITH CHECK OPTION. WITH CHECK OPTION will make sure that all INSERT and UPDATE statements executed against the view meet the restrictions in the

  WHERE clause, and that the modified data in the view remains visible after INSERT and UPDATE statements.

  Step 2-3:

  INSTEAD OF triggers can be created on a view to make a view updatable.

  References:

  http://zarez.net/?p=3002

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

• Question 20:

  You are optimizing the performance of a batch update process. You have tables and indexes that were created by running the following Transact-SQL statements:

  

  The following query runs nightly to update the isCreditValidated field:

  

  You review the database and make the following observations:

  Most of the IsCreditValidated values in the Invoices table are set to a value of 1.

  There are many unique InvoiceDate values.

  The CreditValidation table does not have an index.

  Statistics for the index IX_invoices_CustomerID_Filter_IsCreditValidated indicate there are no individual seeks but multiple individual updates.

  You need to ensure that any indexes added can be used by the update query. If the IX_invoices_CustomerId_Filter_IsCreditValidated index cannot be used by the query, it must be removed. Otherwise, the query must be modified to use with

  the index.

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

  NOTE: Each correct selection is worth one point.

  A. Add a filtered nonclustered index to Invoices on InvoiceDate that selects where IsCreditNote= 1 and IsCreditValidated = 0.
  B. Rewrite the update query so that the condition for IsCreditValidated = 0 precedes the condition for IsCreditNote = 1.
  C. Create a nonclustered index for invoices in IsCreditValidated, InvoiceDate with an include statement using IsCreditNote and CustomerID.
  D. Add a nonclustered index for CreditValidation on CustomerID.
  E. Drop the IX_invoices_CustomerId_Filter_IsCreditValidatedIndex.
  A. Add a filtered nonclustered index to Invoices on InvoiceDate that selects where IsCreditNote= 1 and IsCreditValidated = 0.
  B. Rewrite the update query so that the condition for IsCreditValidated = 0 precedes the condition for IsCreditNote = 1.
  E. Drop the IX_invoices_CustomerId_Filter_IsCreditValidatedIndex.

  ---

  A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.

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