Q1. You plan to execute the following code:
You need to identify how many rows will be in dbo.Table1 after you execute the code.
How many rows should you identify?
A. 0
B. 1
C. 2
D. 3
Answer: A
Q2. You have an index for a table in a SQL Azure database. The database is used for Online
Transaction Processing (OLTP).
You discover that the index consumes more physical disk space than necessary.
You need to minimize the amount of disk space that the index consumes.
What should you set from the index options?
A. STATISTICS_NORECOMPUTE = OFF
B. FILLFACTOR = 80
C. FILLFACTOR = 0
D. STATISTICS_NORECOMPUTE = ON
Answer: C
Explanation:
http://msdn.microsoft.com/en-us/library/ms177459.aspx http://msdn.microsoft.com/en-us/library/ms188783.aspx
Q3. 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
Q4. 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
Q5. 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
Q6. You have a Microsoft SQL Azure database that contains a table named Customers.
You have a table-valued function named TopCustomers that returns a list of all the customers that have purchased items during the last 12 months. The ID of the customer is passed as an argument to the TopCustomers function.
You need to create a query that returns a list of all the Customer names and the purchase dates.
The solution must return only customers that have purchased an item during the last 12 months.
What should you add to the query?
A. OUTER JOIN
B. CROSS JOIN
C. CROSS APPLY
D. OUTER APPLY
Answer: C
Q7. 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
Q8. Topic 8)
You have a database named database1. Each table in database1 has one index per column.
Users often report that creating items takes a long time.
You need to perform the following maintenance tasks:
. Identify unused indexes.
. Identify indexes that need to be defragmented. What should you use?
To answer, drag the appropriate function to the correct management task in the answer area. (Answer choices may be used once, more than once, or not at all.)
Answer:
Q9. You use SQL Server 2012 to store data used by an e-commerce application.
You develop a stored procedure named sp1. Sp1 is used to read the price of all the products sold on the e-commerce site.
You need to ensure that sp1 can read data even while another transaction is modifying the price of a product. Sp1 must only read committed data.
Which transaction isolation level should you use in sp1?
A. Serializable
B. Snapshot
C. Repeatable read
D. Read committed
Answer: B
Q10. While testing the CategoryFromType function, you discover that the function is returning 'Other'.
You need to update CategoryFromType to return the category name.
Which line of code should you modify in CategoryFromType.sql?
A. 04
B. 05
C. 12
D. 14
Answer: B