Free Microsoft 70-464 Exam Dumps Questions & Answers

You need to implement a solution that meets the job application requirements.
What should you do?

• A.  Add a UNIQUE constraint to the Applications table on the ApplicationID column and CandidateID column.
• B.  Create a one-to-one relationship between the Openings table and the Applications table.
• C.  Add a UNIQUE constraint to the Applications table on the OpeningID column and the CandidateID column.
• D.  Create a one-to-one relationship between the Candidates table and the Applications table.

You administer a SQL Server 2014 instance.
You have been assigned to determine the cause of frequent long-running transactions that have been tracked to the dbo.Account table, where there are many cases of blocking and deadlocks. The dbo.Account table contains more than one million rows.
Users and processes frequently search for and update data by using the AccountId column, and less frequently the AccountNumber and GovernmentId columns, all of which contain only unique values. Users frequently get lists of AccountNumber values by searching on Last Name and then First Name.
You need to modify the structure of the dbo.Account table to alleviate the issues.
How should you complete the table definition to reduce contention on the table structure? To answer, drag the appropriate code snippets to the correct locations in the CREATE TABLE statement. Each code snippet 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.

Correct Answer:

Explanation:
Note:
Users and processes frequently search for and update data by using the AccountId column (Primary Key Clustered) , and less frequently the AccountNumber (Unique Clustered) and GovernmentId(Unique Clustered) columns, all of which contain only unique values. Users frequently get lists of AccountNumber values by searching on Last Name and then First Name (LastName, Firstname) INCLUDE (AccountNumber).

You review a query that runs slowly. The query accesses data in a table named Schema1.Table1.
The following is the relevant portion of the execution plan for the query:

You need to create the missing index.
Which code segment should you execute?

• A.  CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1)
• B.  CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1) INCLUDE (Column4) WHERE Column2 <> Column3
• C.  CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1, Column2, Column3) INCLUDE (Column4)
• D.  CREATE NONCLUSTERED INDEX IX1 on schema1.Table1 (Column1) INCLUDE (Column4)

You need to recommend a solution to ensure that SQL1 supports the auditing requirements of usp_UpdateSpeakerName.
What should you include in the recommendation?

• A.  Transactional replication
• B.  The Distributed Transaction Coordinator (DTC)
• C.  Change tracking
• D.  Change data capture

You have a SQL Azure database.
You execute the following script:

You add 1 million rows to Table1. Approximately 85 percent of all the rows have a null value for Column2.
You plan to deploy an application that will search Column2.
You need to create an index on Table1 to support the planned deployment. The solution must minimize the storage requirements.
Which code segment should you execute?

• A.  CREATE INDEX IX_Table1 ON Table1 (Column2)
  WITH FILLFACTOR-0
• B.  CREATE INDEX IX_Table1 ON Table1 (Column2)
  WHERE Column2 IS NULL
• C.  CREATE INDEX IX_Table1 ON Table1 (Column2)
  WHERE Column2 IS NOT NULL
• D.  CREATE INDEX IX_Table1 OK Table1 (Column1)
  INCLUDE (Column2)