★ 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
Act now and download your Microsoft 70-464 test today! Do not waste time for the worthless Microsoft 70-464 tutorials. Download Up to the minute Microsoft Developing Microsoft SQL Server 2012 Databases exam with real questions and answers and begin to learn Microsoft 70-464 with a classic professional.
2021 Sep 70-464 study guide:
Q71. You need to implement a solution that meets the data recovery requirements. You update each stored procedure to accept a parameter named @transactionID. What should you add next to the beginning of each stored procedure?
A. SAVE TRANSACTION WITH MARK @transactionID
B. ROLLBACK DISTRIBUTED TRANSACTION @transactionID
C. BEGIN TRANSACTION WITH MARK @transactionID
D. COMMIT TRANSACTION @transactionID
Answer: C
Q72. You execute IndexManagement.sql and you receive the following error message: "Msg 512, Level 16, State 1, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =,!
=, <, <= ,>, > = or when the subquery is used as an expression."
You need to ensure that IndexManagement.sql executes properly.
Which WHILE statement should you use at line 18?
A. WHILE SUM(@RowNumber) < (SELECT @counter FROM @indextable)
B. WHILE @counter < (SELECT COUNT(RowNumber) FROM @indextable)
C. WHILE COUNT(@RowNumber) < (SELECT @counter FROM @indextable)
D. WHILE @counter < (SELECT SUM(RowNumber) FROM @indextabie)
Answer: B
Q73. You need to identify which long running transactions use an index. Which dynamic management view should you use?
A. sys.dm_exec_query_optimizer_info
B. sys.dm_exec_connections
C. sys.dm_exec_query_stats
D. sys.dm_exec_sessions
Answer: A
Q74. You have an index for a table in a SQL Azure database. The database is used for Online Transaction Processing (OLTP).
You discover that many page splits occur when records are inserted or updated in the table.
You need to minimize the number of page splits.
What should you set from the index options?
A. FILLFACTOR = 0
B. STATISTICS_NORECOMPUTE = OFF
C. STATISTICS_NORECOMPUTE = ON
D. FILLFACTOR = 80
Answer: D
Explanation:
http://msdn.microsoft.com/en-us/library/ms188783.aspx http://msdn.microsoft.com/en-us/library/ms177459.aspx
Q75. You execute usp_TestSpeakers.
You discover that usp_SelectSpeakersByName uses inefficient execution plans.
You need to update usp_SelectSpeakersByName to ensure that the most efficient
execution plan is used.
What should you add at line 30 of Procedures.sql?
A. OPTION (FORCESCAN)
B. OPTION (FORCESEEK)
C. OPTION (OPTIMIZE FOR UNKNOWN)
D. OPTION (OPTIMIZE FOR (@LastName= 'Anderson'))
Answer: C
Explanation:
http://msdn.microsoft.com/en-us/library/ms181714.aspx
Replace vtc 70-464:
Q76. Your company has a SQL Azure subscription.
You implement a database named Database1. Database1 has two tables named Table1 and Table2.
You create a stored procedure named sp1. Sp1 reads data from Table1 and inserts data into Table2.
A user named User1 informs you that he is unable to run sp1.
You verify that User1 has the SELECT permission on Table1 and Table2.
You need to ensure that User1 can run sp1. The solution must minimize the number of permissions assigned to User1.
What should you do?
A. Change sp1 to run as the saUser.
B. Grant User1 the EXECUTE permission on sp1.
C. Add User1 to the db_datawriter role.
D. Grant User1 the INSERT permission on Table2.
Answer: B
Explanation:
http://msdn.microsoft.com/en-us/library/ms191291.aspx
Q77. You need to implement a solution that addresses the page split issues.
Which statement should you execute?
A. ALTER INDEX IX_Orders_ShipDate ON Orders
REBUILD WITH (PAD_INDEX=OFF, DROP_EXISTING = ON);
B. ALTER INDEX IX_Orders_ShipDate ON Orders
REBUILD WITH (FILLFACTOR=50, DROP_EXISTING = ON);
C. ALTER INDEX IX_Orders_ShipDate ON Orders
REBUILD WITH (FILLFACTOR = 0, DROP_EXISTING = ON);
D. ALTER INDEX IX_Orders_ShipDate ON Orders
REBUILD WITH (PAD_INDEX=ON, DROP_EXISTING = ON);
Answer: B
Q78. 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:
Q79. You have a Microsoft SQL Azure database. You have the following stored procedure:
You discover that the stored procedure periodically fails to update Person.Contact.
You need to ensure that Person.Contact is always updated when UpdateContact executes. The solution must minimize the amount of time required for the stored procedure to execute and the number of locks held.
What should you do?
A. Add the following line of code to line 12:
WITH (UPDLOCK)
B. Add the following line of code to line 05:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
C. Add the following line of code to line 08:
WITH (UPDLOCK)
D. Add the following line of code to line 05:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
Answer: C
Explanation: * Overall, you should use UPDLOCK when you read a value that you plan to update later in the same transaction to prevent the value from changing.
* UPDLOCK Specifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for read operations only at the row-level or page-level. If UPDLOCK is combined with TABLOCK, or a table-level lock is taken for some other reason, an exclusive (X) lock will be taken instead. When UPDLOCK is specified, the READCOMMITTED and READCOMMITTEDLOCK isolation level hints are ignored. For example, if the isolation level of the session is set to SERIALIZABLE and a query specifies (UPDLOCK, READCOMMITTED), the READCOMMITTED hint is ignored and the transaction is run using the SERIALIZABLE isolation level.
Q80. Topic 8)
You are planning two stored procedures named SProc1 and SProc2.
You identify the following requirements:
. SProc1 must return a table.
. SProc2 must return a scalar value. You need to identify which option must be implemented for each stored procedure to return the desired data.
Which options should you identify?
To answer, drag the appropriate option to the correct requirement in the answer area. (Answer choices may be used once, more than once, or not at all.)
Answer: