Free Microsoft 70-464 Exam Dumps Questions & Answers

Your network contains a server that has SQL Server 2014 installed.
You create a table by using the following script:

You need to recommend a solution to ensure that each combination of ProductName and ProductManufacturer is not duplicated.
What should you recommend creating?

• A.  A UNIQUE constraint
• B.  A columnstore index
• C.  A filtered index
• D.  A CHECK constraint

You need to ensure that a new execution plan is used by usp_GetOrdersByProduct each time the stored procedure runs.
What should you do?

• A.  Execute sp_help 'usp_GetOrdersByProduct'.
• B.  Add WITH (FORCESEEK) to line 07 in usp_GetOrdersByProduct.
• C.  Add WITH RECOMPILE to line 03 in usp_GetOrdersByProduct.
• D.  Execute sp_recompile 'usp_GetOrdersByProduct'.

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:
USP_1 cannot allow dirty reads.
USP_2 must place range locks on the data to ensure read consistency.
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:
* read committed
READ COMMITTED
Specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.
* 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.

You have an application that uses a view to access data from multiple tables.
You need to ensure that you can insert rows into the underlying tables by using the view.
What should you do?

• A.  Create an INSTEAD OF trigger on the view.
• B.  Define the view by using the SCHEMABINDING option.
• C.  Define the view by using the CHECK option.
• D.  Materialize the view.

You are designing a database for a university. The database will contain two tables named Classes and StudentGrades that have the following specifications:
Classes will store brochures in the XPS format.
The brochures must be structured in folders and must be accessible byusing UNC paths.
StudentGrades must be backed up on a separate schedule than the rest ofthe database.
You need to identify which SQL Server technology meets the specifications of each table. Which technologies should you identify? To answer, drag the appropriate technology to the correct table in the answer area.

Correct Answer:

Explanation:
http://msdn.microsoft.com/en-us/library/gg471497.aspx
http://msdn.microsoft.com/en-us/library/ff929144.aspx
http://msdn.microsoft.com/en-us/library/ms189563.aspx
http://msdn.microsoft.com/en-us/library/ms190174.aspx
http://msdn.microsoft.com/en-us/library/ms187956.aspx