70-464 Exam
Regenerate 70-464 study guide Guide
Cause all that matters here is passing the Microsoft 70-464 exam. Cause all that you need is a high score of 70-464 Developing Microsoft SQL Server 2012 Databases exam. The only one thing you need to do is downloading Examcollection 70-464 exam study guides now. We will not let you down with our money-back guarantee.
Q1. You need to identify the cause of the page split issues.
Which SQL Server feature should you use?
A. DBCC REINDEX
B. SQL Server Profiler
C. Extended Events
D. DBCC TRACEOFF
Answer: C
46. You need to design a solution that meets the refactoring requirements.
Which type of object should you include in the solution?
A. An indexed view
B. An aggregate function
C. A distributed view
D. A table-valued function
Answer: D
Q2. You are creating a table to support an application that will cache data outside of SQL Server.
The application will detect whether cached values were changed before it updates the
values.
You need to create the table, and then verify that you can insert a row into the table.
Which code segment should you use?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
Explanation:
http://msdn.microsoft.com/en-us/library/ms182776.aspx http://msdn.microsoft.com/en-us/library/ms187942.aspx http://msdn.microsoft.com/en-us/library/ms190348.aspx
Q3. You need to create the InvoiceStatus table in DB1.
How should you define the InvoiceID column in the CREATE TABLE statement?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
Q4. You have a SQL Server 2012 database named Database1. Database1 contains a table named OrderDetails.
For a given sales order, you need to retrieve the OrderID, Quantity, and LineTotal columns for all of the items in the OrderDetails table. The solution must ensure that the results can be joined to other tables.
Which code segment should you execute?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A
Q5. You have a database named Database1. Database1 has two stored procedures named Proc1 and Proc2 and a table named Table1. Table1 has millions of rows.
Proc1 updates data in Table1. Proc2 reads data from Table1.
You discover that when Proc1 is executed to update more than 4,000 rows, Proc2 is blocked. The block affects all rows, including those that are not being updated by Proc1.
You need to ensure that when Proc1 is executing, Proc2 can access the data in Table1 that Proc1 is not updating.
What should you change Proc1 to do?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Update less than 4,000 rows simultaneously.
B. Use the PAGLOCK table hint.
C. Wait for Proc2 to complete.
D. Use the ROWLOCK table hint.
Answer: A
Q6. You have a SQL Server 2012 environment that contains two servers. The servers are configured as shown in the following table.
After the failover is complete, a user receives the following error message when connecting to DB1 on Server2:
"Msg 916, Level 14, State 1, Line 1 The server principal "Account1" is not able to access the database "DB1" under the current security context."
You verify that there is a server login for Account1 on Server2.
You need to ensure that Account1 can connect to DB1.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Update the SID for Account1 on DB1.
B. Add Account1 to the db_datareader role.
C. Create a new database user on DB1.
D. Implement Windows authentication.
Answer: B
Q7. 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 SUM(RowNumber) FROM @indextable)
C. WHILE COUNT(@RowNumber) < (SELECT @counter FROM @indextable)
D. WHILE @counter < (SELECT COUNT(RowNumber) FROM @indextable)
Answer: D
Q8. You need to implement a solution that meets the locking requirements. Which line of code should you modify?
A. Change line 07 in usp_UpdateOpening to: UPDATE Openings WITH (UPDLOCK)
B. Change line 09 in usp_GetOpenings to: FROM Openings o (ROWLOCK)
C. Change line 07 in usp_UpdateOpening to: UPDATE Openings WITH (READPAST)
D. Change line 09 in usp_GetOpenings to: FROM Openings o (NOLOCK)
Answer: D
Q9. You need to recommend a solution to ensure that SQL1 supports the auditing requirements of usp_UpdateEmployeeName.
What should you include in the recommendation?
A. Change data capture
B. Change tracking
C. Transactional replication
D. The Distributed Transaction Coordinator (DTC)
Answer: D
Q10. You have a SQL Server 2012 database named Database1. You execute the following code:
You insert 3 million rows into Sales.
You need to reduce the amount of time it takes to execute Proc1.
What should you do?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A