70-762 Exam
Tips to Pass 70-762 Exam (21 to 30)
It is impossible to pass Microsoft 70-762 exam without any help in the short term. Come to Examcollection soon and find the most advanced, correct and guaranteed Microsoft 70-762 practice questions. You will get a surprising result by our Improved Developing SQL Databases (beta) practice guides.
Q21. HOTSPOT
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:
The application uses views to control access to data. Views must meet the following requirements:
Exhibit
Users must only be able to modify data in the Employee table by using the vwEmployee view. You must prevent users from viewing the view definition in catalog views.
You need to identify the view attribute to use when creating vwEmployee. In the table below, identify the attributes that you must use.
NOTE: Make only one selection in each column.
Answer:
Explanation:
References: https://msdn.microsoft.com/en-us/library/ms187956.aspx
Q22. 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
Answer: B
Explanation:
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 thatare members of an assembly created in the .NET Framework and uploaded in SQL Server.
References:https://msdn.microsoft.com/en-us/library/ms178110.aspx
Q23. 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.
The Account table was created using the following Transact-SQL statement:
There are more than 1 billion records in the Account table. The Account Number column uniquely identifies each account. The ProductCode column has 100 different values. The values are evenly distributed in the table. Table statistics are refreshed and up to date.
You frequently run the following Transact-SQL SELECT statements:
You must avoid table scans when you run the queries. You need to create one or more indexes for the table. Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
A. Yes
B. No
Answer: A
Explanation:
Create a clustered index on theAccountNumber column as it is unique. Create a nonclustered index that includes the ProductCode column.
References:https://msdn.microsoft.com/en-us/library/ms190457.aspx
Q24. HOTSPOT
You create a database table named FactSales by running the following Transact-SQL statements:
You must optimize the indexes without making changes to the ix_FactSales_EmployeeKey index.
You need to implement a columnstore index for the table.
How should you complete the Transact-SQL code? To answer, select the appropriate Transact-SQL segments in the answer area.
NOTE: Each correct selection is worth one point.
Answer:
Q25. Note: This question is part of a series of questions that use the same 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 this series. Information and details provided in a question apply only to that question.
You are developing an application to track customer sales. You create tables to support the application. You need to create a database object that meets the following data entry requirements:
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
Answer: C
Explanation:
References: https://msdn.microsoft.com/en-us/library/ms345075.aspx
Q26. 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:
Solution: You create a stored procedure that includes the following Transact-SQL segment:
Does the solution meet the goal?
A. Yes
B. No
Answer: B
Explanation:
References: http://stackoverflow.com/questions/11444923/stored-procedure-to-update- multiple-tables
Q27. 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 stores information about the employees of your company.
You need to implement and enforce the following business rules: Solution: You implement a FOR UPDATE trigger on the table. Does the solution meet the goal?
A. Yes
B. No
Answer: B
Explanation:
References: http://stackoverflow.com/questions/16081582/difference-between-for-update-of-and-for-update
Q28. 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.
You are developing a new application that uses a stored procedure. The stored procedure inserts thousands of records as a single batch into the Employees table.
Users report that the application response time has worsened since the stored procedure was updated. You examine disk-related performance counters for the Microsoft SQL Server instance and observe several high values that include a disk performance issue. You examine wait statistics and observe an unusually high WRITELOG value.
You need to improve the application response time.
Solution: You update the application to use implicit transactions when connecting to the database.
Does the solution meet the goal?
A. Yes
B. No
Answer: B
Explanation:
References: http://sqltouch.blogspot.co.za/2013/05/writelog-waittype-implicit-vs- explicit.html
Q29. 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.
You are developing a new application that uses a stored procedure. The stored procedure inserts thousands of records as a single batch into the Employees table.
Users report that the application response time has worsened since the stored procedure was updated. You examine disk-related performance counters for the Microsoft SQL Server instance and observe several high values that include a disk performance issue. You examine wait statistics and observe an unusually high WRITELOG value.
You need to improve the application response time.
Solution: You replace the stored procedure with a user-defined function. Does the solution meet the goal?
A. Yes
B. No
Answer: B
Explanation:
References: https://msdn.microsoft.com/en-us/library/ms345075.aspx
Q30. 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:
- Employee ID
- 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:
Does the solution meet the goal?
A. Yes
B. No
Answer: A
Explanation:
Datetimeoffset defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.
Syntaxis: datetimeoffset [ (fractional seconds precision) ]
Forthe use"datetimeoffset(0)", the Fractional seconds precision is 0, which is required here. References:https://msdn.microsoft.com/en-us/library/bb630289.aspx