70-762 Exam
Shortcuts To 70-762（21 to 30）
Your success in Microsoft 70-762 is our sole target and we develop all our 70-762 braindumps in a way that facilitates the attainment of this target. Not only is our 70-762 study material the best you can find, it is also the most detailed and the most updated. 70-762 Practice Exams for Microsoft MCSA 70-762 are written to the highest standards of technical accuracy.
Q21. DRAG DROP
You have a database named MyDatabase. You must monitor all the execution plans in XML format by using Microsoft SQL Trace. The trace must meet the following requirements:
- Capture execution plans only for queries that run the MyDatabase database.
- Filter out plans with event duration of less than or equal to 100 microseconds.
- Save trace results to a disk on the server. You need to create the trace.
In which order should you arrange the Transact-SQL segments to develop the solution? To answer, move all Transact-SQL segments to the answer area and arrange them in the correct order.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.
DECLARE @traceEventId int = 122;
DECLARE @traceColumnIdForTextData int = 1; DECLARE @durationFilter bigint = 100 DECLARE @databaseID int;
SELECT @databaseId = DB_ID(‘MyDatabase’);
Answer:
Explanation:
The following system stored procedures are used to define and manage traces:
* sp_trace_create is used to define a trace and specify an output file location as well asother options that I’ll cover in the coming pages. This stored procedure returns a handle to the created trace, in the form of an integer trace ID.
* sp_trace_setevent is used to add event/column combinations to traces based on the trace ID, as well as toremove them, if necessary, from traces in which they have already been defined.
* sp_trace_setfilter is used to define event filters based on trace columns.
* sp_trace_setstatus is called to turn on a trace, to stop a trace, and to delete a trace definitiononce you’re done with it. Traces can be started and stopped multiple times over their lifespan.
References:https://msdn.microsoft.com/en-us/library/cc293613.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. 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:
- 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.
Answer:
Explanation:
Box 1: UPDATE STATISTICS Box 2: SAMPLE 20 PERCENT
UPDATE STATISTICS tablenameSAMPLE number { PERCENT | ROWS }
Specifies the approximate percentage or number of rows in the table or indexed view for the query optimizer to use when it updates statistics. For PERCENT, number can be from 0 through 100 and for ROWS, number can be from0 to the total number of rows.
Box 3: UPDATE STATISTICS Box 4: WITH FULLSCAN
FULLSCAN computes statistics by scanning all rows in the table or indexed view. FULLSCAN and SAMPLE 100 PERCENT have the same results. FULLSCAN cannot be used with the SAMPLE option.
References:https://msdn.microsoft.com/en-us/library/ms187348.aspx
Q24. 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
Q25. 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
Answer: D
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.
References:https://msdn.microsoft.com/en-us/library/ms178110.aspx
Q26. 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
Q27. 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: B
Explanation:
Datetimeoffset, not datetimeofset, defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hourclock.
Syntaxis: datetimeoffset [ (fractional seconds precision) ]
For the use "datetimeoffset", the Fractional seconds precision is 7. References:https://msdn.microsoft.com/en-us/library/bb630289.aspx
Q28. 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
Q29. You are experiencing performance issues with the database server.
You need to evaluate schema locking issues, plan cache memory pressure points, and backup I/O problems.
What should you create?
A. a System Monitor report
B. a sys.dm_tran_database_transaction dynamic management view query
C. an Extended Events session that uses Query Editor
D. an Activity Monitor session in Microsoft SQL Management Studio.
Answer: D
Explanation:
References: https://msdn.microsoft.com/en-us/library/hh212951.aspx
Q30. DRAG DROP
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 the series.
You have a database named Sales that contains the following database tables. Customer, Order, and Products. The Products table and the order table shown in the following diagram.
The Customer table includes a column that stores the date 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.
You need to begin to modify the table design to adhere to third normal form.
Which column should you remove for each table? To answer? drag the appropriate column names to the correct locations. Each column name 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.
Answer:
Explanation:
In the Products table the SupplierName is dependant on the SupplierID, not on the ProductID.
In the Orders table the ProductName is dependant on the ProductID, not on the OrderID.
Note:
A table is in third normal form when the following conditions are met:
* It is in second normal form.
* All nonprimary fields are dependent on the primarykey.
Second normal form states that it should meet all the rules for First 1Normnal Form and there must be no partial dependences of any of the columns onthe primary key.
First normal form (1NF) sets the very basic rules for an organized database:
* Define the data items required, because they become the columns in a table. Place
related data items in a table.
* Ensure that there are no repeating groups ofdata.
* Ensure that there is a primary key. References:https://www.tutorialspoint.com/sql/third-normal-form.htm