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

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

Question No.61

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:

image

Does the solution meet the goal?

  1. Yes

  2. No

Correct Answer: B

Explanation:

http://stackoverflow.com/questions/11444923/stored-procedure-to-update-multiple-tables

Question No.62

DRAG DROP

You need to build a function that meets the following requirements: How should you complete the Transact-SQL statement?

To answer, drag the appropriate Transact-SQL statements 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.

image

Correct Answer:

image

Question No.63

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 an object that meet the following requirements:

image

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

image

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

image

Run when a table is created or modified. What should you create?

  1. extended procedure

  2. CLR procedure

  3. user-defined procedure

  4. DML trigger

  5. scalar-valued function

  6. table-valued function

  7. DDL trigger

Correct Answer: B

Explanation:

The common language runtime (CLR) is the heart of the Microsoft .NET Framework and provides 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 No.64

HOTSPOT

You are maintaining statistics for a database table named tblTransaction. The table contains more than 10 million records.

You need to create a stored procedure that meets the following requirements:

image

image

On weekdays, update statistics for a sample of the total number of records in the table. On weekends, update statistics by sampling all rows in the table.

A maintenance task will call this stored procedure daily. How should you complete the stored procedure?

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

image

Correct Answer:

image

Question No.65

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?

  1. Create a sys.dm_os_waiting_tasks query.

  2. Create a sys.dm_exec_sessions query.

  3. Create a Performance Monitor Data Collector Set.

  4. Create a sys.dm_os_memory_objects query.

  5. Create a sp_configure `max server memory#39;query.

  6. Create a SQL Profiler trace.

  7. Create a sys.dm_os_wait_stats query.

  8. Create an Extended Event.

Correct Answer: G

Explanation:

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

Note: This question is part of a series of questions that present the same scenario. Each question in this series contains a unique solution. Determine whether the solution meets the stated goals.

Your company has employees in different regions around the world.

You need to create a database table that stores the following employee attendance information:

image

Employee ID

image

image

date and time employee checked in to work date and time employee checked out of work

Date and time information must be time zone aware and must not store fractional seconds. Solution: You run the following Transact-SQL statement:

image

Does the solution meet the goal?

  1. Yes

  2. No

Correct Answer: B

Explanation:

datetime2 defines adate that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, andoptional user-specified precision.

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

Question No.67

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?

  1. extended procedure

  2. CLR procedure

  3. user-defined procedure

  4. DML trigger

  5. scalar-valued function

  6. table-valued function

Correct Answer: F

Explanation:

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

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.

image

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 uspUpdateCustomerSummary stored procedure to make changes to customer account summaries. Other stored procedures call the spDeleteCustAcctRelationship to delete records from the CustomerToAccountBridge table.

When you start uspUpdateCustomerSummary, there are no active transactions. The procedure fails at the second update statement due to a CHECK constraint violation on the TotalDepositAccountCount column.

What is the impact of the stored procedure on the CustomerDetails table?

  1. The value of the TotalAccountCount column decreased.

  2. The value of the TotalDepositAccountCount column is decreased.

  3. The statement that modifies TotalDepositAccountCount is excluded from the transaction.

  4. The value of the TotalAccountCount column is not changed.

Correct Answer: D

Question No.69

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 create a baseline set of metrics to report how the computer running SQL Server operates under normal load. The baseline must include the resource usage associated with the server processes.

What should you do?

  1. Create a sys.dm_os_waiting_tasks query.

  2. Create a sys.dm_exec_sessions query.

  3. Create a Performance Monitor Data Collector Set.

  4. Create a sys.dm_os_memory_objects query.

  5. Create a sp_configure `max server memory#39; query.

  6. Create a SQL Profiler trace.

  7. Create asys.dm_os_wait_stats query.

  8. Create an Extended Event.

Correct Answer: D

Explanation:

sys.dm_os_memory_objects returns memory objects that are currently allocated by SQL Server. You can usesys.dm_os_memory_objects to analyze memory use and to identify possible memory leaks.

Example:

The following example returns the amount of memory allocated by each memory object type. SELECT SUM (pages_in_bytes) as #39;Bytes Used#39;, type FROMsys.dm_os_memory_objects GROUP BY type

ORDER BY #39;Bytes Used#39; DESC; GO

Question No.70

DRAG DROP

You have a database that contains three encrypted store procedures named dbo.Proc1, dbo.Proc2 and dbo.Proc3. The stored procedures include INSERT, UPDATE, DELETE and BACKUP DATABASE statements.

You have the following requirements:

image

You must run all the stored procedures within the same transaction.

image

image

You must automatically start a transaction when stored procedures include DML statements. You must not automatically start a transaction when stored procedures include DDL statements.

You need to run all three stored procedures.

Which four Transact-SQL segments should you use to develop the solution?

To answer, move the appropriate Transact-SQL segments to the answer area and arrange then in the correct order.

image

Correct Answer:

image

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

You must be logged in to post a comment.

Proudly powered by WordPress   Premium Style Theme by www.gopiplus.com