Microsoft 70-762 Online Practice Questions and Exam Preparation

Microsoft 70-762 Online Questions & Answers

• Question 21:

  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 TRY...CATCH error handling

  -Be written by using Transact-SQL statements.

  What should you create?

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

  ---

  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 usedto enforce business rules and data integrity, query other tables, and include complex Transact-SQL statements.

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

• Question 22:

  You are analyzing the memory usage of a Microsoft SQL Server instance. You need to obtain the information described on the following table.

  

  Which performance counter should you use for each requirement? To answer, drag the appropriate performance counters to the correct requirements. Each performance counter may be used once, more than once or not at all. You may need to drag the split bat between panes or scroll to view content.

  NOTE: Each correct selection is worth one point.

  Select and Place:

  

  

  ---

  Requirement1: SQL Server: Memory Manager: Total Server Memory (KB)

  This counter specifies theamount of memory the server has committed using the memory manager.

  Requirement2: SQL Server: Memory Manager: Granted Workspace Memory (KB)

  Specifies the total amount of memory currently granted to executing processes, such as hash, sort, bulk copy, andindex creation operations.

  Requirement3: Process: working Set Each time a process is created, it reserves the minimum working set size for the process.

  The virtual memory manager attempts to keep enough memory for the minimum working set resident when the process is active, but keeps no more than the maximum size.

  References:

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

  https://blogs.technet.microsoft.com/askperf/2007/05/18/sql-and-the-working-set/

• Question 23:

  You are profiling a frequently used database table named UserEvents. The READ_COMMITED_SNAPSHOT database option is set to OFF.

  In the trace results, you observe that lock escalation occurred for one stored procedure even though the number of locks in the database did not exceed memory or configuration thresholds. Events details are provided in the following table:

  

  You need to modify the uspDeleteEvents stored procedure to avoid lock escalation.

  How should you modify the stored procedure? To answer, select the appropriate Transact-SQL segments in the answer area.

  Hot Area:

  

  

  ---

  Delete up to 4000 rows at a time. Keep doing it until all rows have been deleted.

  Note that @@ROWCOUNT returnsthe number of rows affected by the last statement.

  References:

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

• Question 24:

  You are analyzing the performance of a database environment.

  You need to find all unused indexes in the current database.

  How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.

  Hot Area:

  

  

  ---

  Example: Following query helps you to find all unused indexes within database using sys.dm_db_index_usage_stats DMV.

  -- Ensure a USE statement has been executed first.

  SELECT u.*

  FROM [sys].[indexes] i

  INNER JOIN[sys].[objects] o ON (i.OBJECT_ID = o.OBJECT_ID)

  LEFT JOIN [sys].[dm_db_index_usage_stats] u ON (i.OBJECT_ID = u.OBJECT_ID)

  AND i.[index_id] = u.[index_id] AND u.[database_id] = DB_ID() --returning the database ID of the current database WHERE o.[type] <>'S' --shouldn't be a system base table AND i.[type_desc] <> 'HEAP' AND i.[name] NOT LIKE 'PK_%' AND u.[user_seeks] + u.[user_scans] + u.[user_lookups] = 0 AND u.[last_system_scan] IS NOT NULL ORDER BY 1 ASC References:https://basitaalishan.com/2012/06/15/find-unused-indexes-using-sysdm_db_index_usage_stats/

• Question 25:

  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.
  D. Update the transaction level of the report query session to READ UNCOMMITTED.

  ---

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

  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 DB1 that contains the following tables: Customer, CustomerToAccountBridge, and CustomerDetails. The three tables are part of the Sales schema. The database also contains a schema named Website. You

  create the Customer table by running the following Transact-SQL statement:

  

  The value of the CustomerStatus column is equal to one for active customers. The value of the Account1Status and Account2Status columns are equal to one for active accounts. The following table displays selected columns and rows from the Customer table.

  

  You plan to create a view named Website.Customer and a view named Sales.FemaleCustomers. Website.Customer must meet the following requirements:

  1. Allow users access to the CustomerName and CustomerNumber columns for active customers.

  2. Allow changes to the columns that the view references. Modified data must be visible through the view.

  3. Prevent the view from being published as part of Microsoft SQL Server replication.

  Sales.Female.Customers must meet the following requirements:

  1. Allow users access to the CustomerName, Address, City, State and PostalCode columns.

  2. Prevent changes to the columns that the view references.

  3. Only allow updates through the views that adhere to the view filter.

  You have the following stored procedures: spDeleteCustAcctRelationship and spUpdateCustomerSummary. The spUpdateCustomerSummary stored procedure was created by running the following Transacr-SQL statement:

  

  You run the spUpdateCustomerSummary stored procedure to make changes to customer account summaries. Other stored procedures call the spDeleteCustAcctRelationship to delete records from the CustomerToAccountBridge table.

  When a procedure calls spDeleteCustAcctRelationship, if the calling stored procedures has already started an active transaction, all the detections made by the spDeleteCustAccRelationship stored procedure must be committed by the caller;

  otherwise changes must be committed within the spDeleteCustAcctRelationship stored procedure.

  If any error occurs during the delete operation, only the deletes made by the soDeleteCustACCTRelationships stored procedure must be rolled back and the status must be updated.

  You need to complete the stored procedure to ensure all the requirements are met.

  How should you complete the procedure? To answer, drag the Transact-SQL segments to the correct location. 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.

  NOTE: Each correct selection is worth one point.

  Select and Place:

  

  

  ---

  Savepoints offer a mechanism to roll back portions of transactions. You create a savepoint using the SAVE TRANSACTION savepoint_name statement. Later, you execute a ROLLBACK TRANSACTION savepoint_name statement to roll back to the savepoint instead of rolling back to the start of the transaction.

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

• Question 27:

  You are analyzing the performance of a database environment.

  Applications that access the database are experiencing locks that are held for a large amount of time. You are experiencing isolation phenomena such as dirty, nonrepeatable and phantom reads.

  You need to identify the impact of specific transaction isolation levels on the concurrency and consistency of data.

  What are the consistency and concurrency implications of each transaction isolation level?

  To answer, drag the appropriate isolation levels to the correct locations. Each isolation level 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:

  

  

  ---

  Read Uncommitted (aka dirty read): A transaction T1executing under this isolation level can access data changed by concurrent transaction(s).

  Pros:No read locks needed to read data (i.e. no reader/writer blocking). Note, T1 still takes transaction duration locks for any data modified.

  Cons: Data is notguaranteed to be transactionally consistent.

  Read Committed: A transaction T1 executing under thisisolation level can only access committed data.

  Pros: Good compromise between concurrency and consistency.

  Cons: Locking and blocking. The data can change when accessed multiple times within the same transaction.

  Repeatable Read: A transaction T1 executing under this isolation level can only access committed data with an additional guarantee that any data read cannot change (i.e. it is repeatable) for the duration of the transaction.

  Pros: Higher data consistency.

  Cons: Locking and blocking. The S locks are held for the duration of the transaction that can lower the concurrency. It does not protect against phantom rows.

  Serializable: A transaction T1 executing under this isolation level provides the highest data consistency including elimination of phantoms but at the cost of reduced concurrency. It prevents phantoms by taking a range lock or table level lock if range lock can't be acquired (i.e. no index on the predicate column) for the duration of the transaction.

  Pros: Full data consistency including phantom protection.

  Cons: Locking and blocking. The S locks are held for the duration of the transaction that can lower the concurrency.

  References:

  https://blogs.msdn.microsoft.com/sqlcat/2011/02/20/concurrency-series-basicsof-transaction-isolation-levels/

• Question 28:

  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 user-defined function on the Employees table.

  Does the solution meet the goal?

  A. Yes
  B. No
  A. Yes

  ---

  SQL Server 2016 provides two features that track changes to data in a database: change data capture and change tracking. These features enable applications to determine the DML changes (insert, update, and delete operations) that were

  made to user tables in a database.

  Change data is made available to change data capture consumers through table-valued functions (TVFs).

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

• Question 29:

  You have a trigger named CheckTriggerCreation that runs when a user attempts to create a trigger. The CheckTriggerCreation trigger was created with the ENCRYPTION option and additional proprietary business logic.

  You need to prevent users from running the ALTER and DROP statements or the sp_tableoption stored procedure.

  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.

  Select and Place:

  

  

• Question 30:

  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.

  Changes to the price of any product must be less a 25 percent increase from the current price. The shipping department must be notified about order and shipping details when an order is entered into the database.

  You need to implement the appropriate table objects.

  Which object should you use for each table? To answer, drag the appropriate objects to the correct tables. Each object 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:

  

  

  ---

  The Products table needs a primary key constraint on the ProductID field.

  The Orders table needs a foreign key constraint on the productID field, with a reference to the ProductID field in the Products table.