Microsoft Microsoft Certifications 70-762 Questions & Answers

• Question 11:

  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

  Correct Answer: B

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

  Correct Answer: D

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

  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. DML 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 programmingmodel 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 14:

  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 and application to track customer sales.

  You need to return the sum of orders that have been finalized, given a specified order identifier. This value will be used in other Transact-SQL statements.

  You need to create a database object.

  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

  Correct Answer: F

  User-defined scalar functions return a single data value of the type defined in the RETURNS clause.

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

• Question 15:

  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 and application to track customer sales.

  You need to create an object that meet the following requirements:

  - Run managed code packaged in an assembly that was created in the Microsoft.NET Framework and uploaded in Microsoft SQL Server.

  -

  Run written a transaction and roll back if a future occurs.

  -

  Run when a table is created or modified.

  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

  Correct Answer: B

  The common language runtime (CLR) is the heart of the Microsoft .NET Framework andprovides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code. With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.

• Question 16:

  You have a view that includes an aggregate.

  You must be able to change the values of columns in the view. The changes must be reflected in the tables that the view uses.

  You need to ensure that you can update the view.

  What should you create?

  A. table-valued function

  B. a schema-bound view

  C. a partitioned view

  D. a DML trigger

  Correct Answer: D

• Question 17:

  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 independent of the other questions in this series.

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

  You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a RAID array. The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run

  during off-peak hours.

  You must monitor and optimize the SQL Server to maximize throughput, response time, and overall SQL performance.

  You need to identify previous situations where a modification has prevented queries from selecting data in tables.

  What should you do?

  A. Create a sys.dm_os_waiting_tasks query.

  B. Create a sys.dm_exec_sessions query.

  C. Create a Performance Monitor Data Collector Set.

  D. Create a sys.dm_os_memory_objects query.

  E. Create a sp_configure `max server memory' query.

  F. Create a SQL Profiler trace.

  G. Create a sys.dm_os_wait_stats query.

  H. Create an Extended Event.

  Correct Answer: G

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

• Question 18:

  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 independent of the other questions in this series.

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

  You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a RAID array. The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run

  during off-peak hours.

  You observe that many deadlocks appear to be happening during specific times of the day.

  You need to monitor the SQL environment and capture the information about the processes that are causing the deadlocks. Captured information must be viewable as the queries are running.

  What should you do?

  A. A. Create a sys.dm_os_waiting_tasks query.

  B. Create a sys.dm_exec_sessions query.

  C. Create a PerformanceMonitor Data Collector Set.

  D. Create a sys.dm_os_memory_objects query.

  E. Create a sp_configure `max server memory' query.

  F. Create a SQL Profiler trace.

  G. Create a sys.dm_os_wait_stats query.

  H. Create an Extended Event.

  Correct Answer: F

  To view deadlock information, the Database Engine provides monitoring tools in the form of two trace flags, and the deadlock graph event in SQL Server Profiler.

  Trace Flag 1204 and Trace Flag 1222 When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL Server error log. Trace flag 1204 reports deadlock information formatted by each nodeinvolved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources. It is possible to enable bothtrace flags to obtain two representations of the same deadlock event.

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

• Question 19:

  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 independent of the other questions in this series.

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

  You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a RAID array. The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run

  during off-peak hours.

  You must monitor the SQL Server instances in real time and optimize the server to maximize throughput, response time, and overall SQL performance.

  What should you do?

  A. A. Create asys.dm_os_waiting_tasks query.

  B. Create a sys.dm_exec_sessions query.

  C. Create a Performance Monitor Data Collector Set.

  D. Create a sys.dm_os_memory_objects query.

  E. Create a sp_configure `max server memory' query.

  F. Create a SQL Profiler trace.

  G. Create a sys.dm_os_wait_stats query.

  H. Create an Extended Event.

  Correct Answer: B

  sys.dm_exec_sessions returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more. Use sys.dm_exec_sessions to first view the current system load and to identify a session of interest, and then learn more information about that session by using other dynamic management views or dynamic management functions.

  Examples of use include finding long-running cursors, and finding idle sessions that have open transactions.

• Question 20:

  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 independent of the other questions in this series.

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

  You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a RAID array. The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run

  during off-peak hours.

  You must monitor the SQL Server instances in real time and optimize the server to maximize throughput, response time, and overall SQL performance.

  You need to ensure that the performance of each instance is consistent for the same queried and query plans.

  What should you do?

  A. A. Create a sys.dm_os_waiting_tasks query.

  B. Create a sys.dm_exec_sessions query.

  C. Create a Performance Monitor Data Collector Set.

  D. Create a sys.dm_os_memory_objects query.

  E. Create a sp_configure `max server memory' query.

  F. Create a SQL Profiler trace.

  G. Create asys.dm_os_wait_stats query.

  H. Create an Extended Event.

  Correct Answer: H

  Advanced Viewing of Target Data from Extended Events in SQL Server

  When your event session is currently active, you might want to watch the event data in real time, as it is received by the target.

  Management > Extended Events > Sessions > [your-session] > Watch Live Data.

  The query_post_execution_showplan extended event enables you to see the actual query plan in the SQL Server Management Studio (SSMS) UI. When the Details pane is visible, you can see a graph of the query plan on the Query Plan

  tab.

  By hovering over a node on the query plan, you cansee a list of property names and their values for the node.

  

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