★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions
Free Instant Download NEW 70-464 Exam Dumps (PDF & VCE):
Available on:
https://www.certleader.com/70-464-dumps.html
Beginners or veterans within IT industry can locate all kinds of Microsoft Microsoft certification training materials at Ucertify. Whats more, your answers to all the Microsoft 70-464 questions are verified through our professionals who are devoted to making the info understandable. Practising our Microsoft 70-464 exam dumps, you are bound to get with the 70-464 exam. Be among those who pass your Microsoft exam is a how proud thing for you personally. The Microsoft certification will likely be a great aid in hunting work opportunities.
2021 Sep microsoft sql server 2012 development (70-464) course:
Q101. 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
Q102. The database contains a disk-based table named ContentTable that has 1 million rows and a column named Fax. Fax allows null values.
You need to update Fax to meet the following requirements:
. Prevent null values from being used.
. Always use an empty string instead of a null value.
Which statement or statements should you execute? (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: A,B,E
Explanation: E: First change the NULLs to ' '.
A: Then set the default to the column to ' '.
B: Finally add the NOT NULL constraint to the column.
Q103. You run the following code:
You need to ensure that the root node of the XML data stored in the Details column is <Order_Details>.
What should you implement?
More than one answer choice may achieve the goal. Select the BEST answer.
A. A user-defined data type
B. An XML index
C. A Data Definition Language (DDL) trigger
D. A data manipulation language (DML) trigger
E. An XML schema collection
Answer: E
Explanation:
http://msdn.microsoft.com/en-us/library/ms187856.aspx
Q104. You have database objects that were created by using the following script:
The dbo.Customers table has 1 million rows.
You discover that usp_GetCustomersByDate takes a long time to complete.
The query plan used by the stored procedure is shown in the exhibit. (Click the Exhibit
button.)
You need to ensure that usp_GetCustomersByDate completes as quickly as possible. What should you do?
A. Modify the stored procedure to include the OPTIMIZE FOR UNKNOWN query hint.
B. Execute the sp_recompile 'dbo.GetCustomersByDate' statement.
C. Execute the ALTER INDEXIX_Customers_CreationDate WITH REBUILD statement.
D. Modify the stored procedure to include the OPTIMIZE FOR('1/1/2008') query hint.
Answer: A
Q105. 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
Replace vtc - microsoft sql server 2012 development (exam 70-464) course:
Q106. Your company has a main office in London and a branch office in New York.
Your network contains a server named Server5 that has SQL Server 2012 installed. Server5 contains a database name ContentDB and a table named ContentTable.
You add an additional server named Server9 that runs SQL Server 2012.
You need to create a distributed partitioned view. The solution must minimize the amount of network traffic.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. Create the view on Server5.
B. Add Server9 as a linked server.
C. Create the view on Server9.
D. Add the Customers table to Server9.
E. Add Server9 as a Distributor.
F. Remove the Customers table from Server5.
Answer: A,B,C,D
Q107. You attempt to process an invoice by using usp_InsertInvoice.sql and you receive the following error message: "Msg 515, Level 16, State 2, Procedure usp_InsertInvoice, Line 10
Cannot insert the value NULL into column 'InvoiceDate', table 'DB1.Accounting.Invoices' column does not allow nulls. INSERT fails."
You need to modify usp_InsertInvoice.sql to resolve the error.
How should you modify the INSERT statement?
A. InvoiceDate varchar(l00) 'InvoiceDate',
B. InvoiceDate varchar(100) 'Customer/InvoiceDate', '
C. InvoiceDate date '@InvoiceDate',
D. InvoiceDate date 'Customer/@InvoiceDate',
Answer: C
Topic 2, Scenario 2
Application Information
You have two servers named SQL1 and SQL2 that have SQL Server 2012 installed.
You have an application that is used to schedule and manage conferences.
Users report that the application has many errors and is very slow.
You are updating the application to resolve the issues.
You plan to create a new database on SQL1 to support the application. A junior database administrator has created all the scripts that will be used to create the database. The script that you plan to use to create the tables for the new database is shown in Tables.sql. The script that you plan to use to create the stored procedures for the new database is shown in StoredProcedures.sql. The script that you plan to use to create the indexes for the new database is shown in Indexes.sql. (Line numbers are included for reference only.)
A database named DB2 resides on SQL2. DB2 has a table named SpeakerAudit that will audit changes to a table named Speakers.
A stored procedure named usp_UpdateSpeakersName will be executed only by other stored procedures. The stored procedures executing usp_UpdateSpeakersName will always handle transactions.
A stored procedure named usp_SelectSpeakersByName will be used to retrieve the names of speakers. Usp_SelectSpeakersByName can read uncommitted data.
A stored procedure named usp_GetFutureSessions will be used to retrieve sessions that will occur in the future.
Procedures.sql
Indexes.sql Tables.sql
11. You are evaluating the index design.
You need to recommend a change to Indexes.sql that will minimize the amount of time it takes for usp_AttendeesReport to execute. The solution must minimize the amount of database fragmentation.
Which line of code should you use to replace line 12 of Indexes.sql?
A. (LastName);
B. (FirstName) INCLUDE (LastName);
C. (LastName, FirstName);
D. (LastName) INCLUDE (FirstName);
Answer: C
Q108. You plan to create a new table that has the following requirements:
...
Uses a GUID data type as the primary key. Uses a clustered index as the primary key. Minimizes fragmentation.
You need to recommend which option to include in the CREATE statement.
Which option should you include?
More than one answer choice may achieve the goal. Select the BEST answer.
A. NEWID
B. @@IDENTITY
C. NEWSEQUENTIALID
D. IDENTITY
Answer: C
Q109. You execute the following code:
You have a stored procedure that includes the following SELECT statement:
You need to create a covering index on UserInfo. Which code segment should you execute?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
Q110. You use SQL Azure to store data used by an e-commerce application.
You develop a stored procedure named sp1. Sp1 is used to read and change the price of all the products sold on the e-commerce site.
You need to ensure that other transactions are blocked from updating product data while sp1 is executing.
Which transaction isolation level should you use in sp1?
A. Repeatable read
B. Read committed
C. Serializable
D. Snapshot
Answer: C