[Free] EnsurePass Microsoft 70-762 Real Exam Dumps Questions 101-110

Get Full Version of the Exam
http://www.EnsurePass.com/70-762.html

Question No.101

You need to implement triggers to automate responses to the following events:

image

image

image

SQL Server logons Database schema changes Database updates

Which trigger types should you use?

To answer, drag the appropriate trigger types to the appropriate scenarios. Each trigger 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.

NOTE: Each correct selection is worth one point.

image

Correct Answer:

image

Question No.102

You plan to create a stored procedure that uses a table parameter as an input parameter. The table value parameter may hold between 1 and 10,000 rows when you run the stored procedure.

The stored procedure will use the rows within the table value parameter to filter the rows that will be returned by the SELECT statement.

You need to create the stored procedure and ensure that it runs as quickly as possible. How should you complete the procedure?

To answer, select the appropriate Transact-SQL segments in the answer area. NOTE: Each correct answer selection is worth one point.

image

Correct Answer:

image

Question No.103

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:

image

You need to resolve the performance issue. Solution: You drop the nonclustered index. Does the solution meet the goal?

  1. Yes

  2. No

Correct Answer: B

Explanation:

We see Actual Number of Row is 3571454, while Estimated Number of Rows is 0. This indicates that the statistics are old, and need to be updated.

Question No.104

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:

image

You need to resolve the performance issue.

Solution: You update statistics for the nonclustered index. Does the solution meet the goal?

  1. Yes

  2. No

Correct Answer: A

Explanation:

We see Actual Number of Row is 3571454, while Estimated Number of Rows is 0. This indicates that the statistics are old, and need to be updated.

Question No.105

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 3-TB database. The database server has 64 CPU cores. You plan to migrate the database to Microsoft Azure SQL Database.

You need to select the service tier for the Azure SQL database. The solution must meet or exceed the current processing capacity.

Solution: You select the Standard service tier. Does this meet the goal?

  1. Yes

  2. No

Correct Answer: B

Explanation:

image

Premium service is required for 3 TB of storage. Single database DTU and storage limits

References:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers-dtu

Question No.106

You are developing queries and stored procedures to support a line-of-business application. You need to use the appropriate isolation level based on the scenario.

Which isolation levels should you implement?

To answer, select the appropriate isolation level for each scenario in the answer area. Each isolation level may be used only one.

NOTE: Each correct selection is worth one point.

image

Correct Answer:

image

Question No.107

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 change the transaction log file size to expand dynamically in increments of 200 MB. Does this meet the goal?

  1. Yes

  2. No

Correct Answer: B

Question No.108

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

image

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

image

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?

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

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

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

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

Correct Answer: A

Explanation:

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 No.109

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:

image

You need to resolve the performance issue. Solution: You defragment both indexes.

Does the solution meet the goal?

  1. Yes

  2. No

Correct Answer: B

Explanation:

We see Actual Number of Row is 3571454, while Estimated Number of Rows is 0. This indicates that the statistics are old, and need to be updated.

Question No.110

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:

image

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.

image

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

image

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

image

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

image

Prevent the view from being published as part of Microsoft SQL Server replication. Sales.Female.Customers must meet the following requirements:

image

image

Allow users access to the CustomerName, Address, City, State and PostalCode columns. Prevent changes to the columns that the view references.

image

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:

image

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.

You must update the design of the Customer table to meet the following requirements.

image

You must be able to store up to 50 accounts for each customer.

image

image

Users must be able to retrieve customer information by supplying an account number. Users must be able to retrieve an account number by supplying customer information.

Which three Transact_SQL segments should you use to develop the solution?

To answer, move the appropriate code blocks from the list of code blocks to the answer area and arrange them in the correct order.

image

Correct Answer:

image

Get Full Version of the Exam
70-762 Dumps
70-762 VCE and PDF

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.