Free Microsoft 070-464 Exam Dumps Questions & Answers

You discover that the usp_GetOrdersAndItems stored procedure takes a long time to complete while usp_AddOrder or usp_AddXMLOrder run.
You need to ensure that usp_GetOrdersAndItems completes as quickly as possible.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

• A.Set the isolation level of the usp_GetOrdersAndItems stored procedure to SNAPSHOT.
• B.Execute the ALTER DATABASE Sales SET ALLOW_SNAPSHOT_ISOLATION ON statement.
• C.Set the isolation level of the usp_AddOrder stored procedure to SNAPSHOT.
• D.Set the isolation level of the usp_AddOrder stored procedure to SERIALIZABLE.
• E.Execute the ALTER DATABASE Sales SET ALLOW_SNAPSHOT_ISOLATION OFF statement.
• F.Set the isolation level of the usp_GetOrdersAndItems stored procedure to SERIALIZABLE.

You need to modify the function in CountryFromID.sql to ensure that the country name is returned instead of the country ID.
Which line of code should you modify in CountryFromID.sql?

• A.05
• B.19
• C.06
• D.04

You have a table named Table1 that stores customer data.
Each customer has a credit limit that can only be discovered by querying multiple tables.
You need to ensure that the value of the credit limit is returned by executing a query on Table1.
What should you create?

• A.A trigger that uses a table-valued function
• B.A calculated column that uses a scalar function
• C.A calculated column that uses a table-valued function
• D.A trigger that uses a ranking function

You need to build a stored procedure that amortizes the invoice amount. Which code segment should you use to create the stored procedure? To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.

Correct Answer:

1 - EXEC sp_configure 'clr enabled','1';
2 - RECONFIGURE;
3 - CREATE ASSEMBLY TaxCalc FROM 'C:\temp\TreyTax.DLL'
4 - CREATE PROCEDURE Accounting.Amortize(@total decimal(8,2), @period int) RETURNS decimal(8,2) AS EXTERNAL NAME TaxCalc TreyResearch.Amortize; Explanation:
http://msdn.microsoft.com/en-us/library/ms131089.aspx
http://msdn.microsoft.com/en-us/library/ms131048.aspx
http://msdn.microsoft.com/en-us/library/ms187926.aspx

You plan to deploy two stored procedures name USP_1 and USP_2 that read data from a database.
Your company identifies the following requirements for each stored procedure:
You need to identify which isolation level you must set for each stored procedure. The solution must minimize the number of locks.
Which isolation level should you identify?
To answer, drag the appropriate isolation level to the correct stored procedure in the answer area. (Answer choices may be used once, more than once, or not at all.)

Correct Answer:

Explanation:
Box 1: read uncommitted
READ UNCOMMITTED is the least restrictive isolation level because it ignores locks placed by other transactions. Transactions executing under READ UNCOMMITTED can read modified data values that have not yet been committed by other transactions; these are called "dirty" reads.
Box 2: SERIALIZABLE
Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.
References: https://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx