Microsoft Recertification for MCSE Data Platform Set 4

You have a SQL Azure database. You execute the following script:
You add 1 million rows to Table1. Approximately 85 percent of all the rows have a null value for Column2.
You plan to deploy an application that will search Column2.
You need to create an index on Table1 to support the planned deployment. The solution must minimize the storage requirements.
Which code segment should you execute?

You are creating a table named Orders. You need to ensure that every time a new row is added to the Orders table, a table that is used for auditing is updated. What should you use? More than one answer choice may achieve the goal. Select the BEST answer.

You have a text file that contains an XML Schema Definition (XSD). You have a table named Schema1.Table1. You have a stored procedure named Schema1.Proc1 that accepts an XML parameter named Param1. You need to store validated XML data in Schema1.Table1. The solution must ensure that only valid XML data is accepted by Param1. What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

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?

You have a SQL Server 2012 database named DB1 that is accessed by 650 concurrent users. You need to log all of the queries to DB1 that become deadlocked. The solution must meet the following requirements:
Provide a representation of the deadlock in XML format.
Minimize the impact on the server.
What should you create?

You have a database named Database1. You execute the following code:
You have the following query. (Line numbers are included for reference only.)
Users report that the query takes a long time to complete.
You create a full-text index for the Notes column.
You need to recommend changes to the query to reduce the amount of time it takes for the query to complete.
Which code segment should you use to replace line 03?

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?

You have a SQL Server 2012 instance that hosts a single-user database. The database does not contain user-created stored procedures or user-created functions. You need to minimize the amount of memory used for query plan caching. Which advanced server option should you modify?

You execute the following code. After populating the Employees table with 10,000 rows, you execute the following query: You need to reduce the amount of time it takes to execute the query. What should you do?

You have a server that has SQL Server 2012 installed. You need to identify which parallel execution plans are running in serial. Which tool should you use?