70-464 Exam
Tips to Pass 70-464 Exam (61 to 70)
Exam Code: 70-464 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Developing Microsoft SQL Server 2012 Databases
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 70-464 Exam.
Q61. 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 filtered index
C. A columnstore index
D. A CHECK constraint
Answer: A
Q62. Topic 8)
You plan to create a custom aggregation function named Median.
You plan to deploy Median to a SQL Server 2014 server named Server1.
You need to ensure that Median can access a web service named WebApp1. The solution
must minimize the number of changes made to the database.
You create a Microsoft .NET Framework class that contains the definition of Median.
You upload a certificate to Server1.
What three tasks should you perform next?
To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Answer:
Q63. Topic 8)
You have a table named Customers that has a clustered index defined on the ID column.
You write a script to create a stored procedure.
You need to complete the script for the stored procedure. The solution must minimize the
number of locks and deadlocks.
What should you do?
To answer, drag the appropriate option to the correct location in the answer area. (Answer
choices may be used once, more than once, or not at all.)
Answer:
Q64. Topic 7)
You need to optimize the index and table structures for POSTransaction.
Which task should you use with each maintenance step? To answer, drag the appropriate tasks to the correct maintenance steps. Each task 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:
Q65. You execute the following code: The execution plan for the query is shown in the exhibit. (Click the Exhibit button.)
You need to prevent the key lookup. What should you modify?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
Q66. You execute usp_SelectEmployeesByName multiple times, passing strings of varying lengths to @LastName. You discover that usp_SelectEmployeesByName uses inefficient execution plans.
You need to update usp_SelectEmployeesByName to ensure that the most efficient execution plan is used.
What should you add at line 31 of StoredProcedures.sql?
A. OPTION (ROBUST PLAN)
B. OPTION (OPTIMIZE FOR UNKNOWN)
C. OPTION (KEEP PLAN)
D. OPTION (KEEPFIXED PLAN)
Answer: B
Explanation:
http://msdn.microsoft.com/en-us/library/ms181714.aspx
Q67. You have a Microsoft SQL Azure database that contains a table named Employees.
You create a non-clustered index named EmployeeName on the name column.
You write the following query to retrieve all of the employees that have a name that starts
with the letters JOH:
You discover that the query performs a table scan.
You need to ensure that the query uses EmployeeName.
What should you do?
A. Recreate EmployeeName as a unique index
B. Recreate EmployeeName as a clustered index
C. Replace LEFT(name,3) = 'JOH' by using name like 'JOH%'
D. Replace LEFT(name,3) = 'JOH' by using substring(name, 1, 3) = 'JOH'
Answer: C
Q68. You need to create a script that automates the export of the XML data. The script must meet the integration requirements.
What should you include in the script?
A. The CREATE SERVER ROLE command and the sp_reassign_proxy, sp_add_job, sp_add_jobstep, and sp_grant_login_to_proxy system stored procedures.
B. The CREATE CREDENTIAL command and the sp_add_proxy, sp_add_job, sp_add_jobstep, and sp_grant_proxy_to_subsystem system stored procedures.
C. The CREATE CREDENTIAL command and the sp_reassign_proxy, sp_add_job, sp_add_jobstep, and sp_grant_login_to_proxy system stored procedures.
D. The CREATE SERVER ROLE command and the sp_add_proxy, sp_add_job, sp_add_jobstep, and sp_grant_proxy_to_subsystem system stored procedures.
Answer: B
Q69. You need to ensure that if any of the statements in usp_UpdateSpeakerName return an error message, all of the changes executed by usp_UpdateSpeakerName are not committed to the database.
What should you do in Procedures.sql? (Each correct answer presents part of the solution. Choose all that apply.)
A. Option A
B. Option B
C. Option C
D. Option D
E. Option E
Answer: B,D
Q70. You execute the following code: You need to select the task that has an IsFinished value of true from the Project that has an Id value of 1.
Which code segment should you use?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: B