Microsoft 070-457 : Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1

You are writing a set of queries against a FILESTREAM-enabled database. You create a stored procedure that will update multiple tables within a transaction. You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back. Which Transact-SQL statement should you include at the beginning of the stored procedure?

• A. SET IMPLICIT_TRANSACTIONS ON
• B. SET IMPLICIT TRANSACTIONS OFF
• C. SET TRANSACTION ISOLATION LEVEL SNAPSHOT
• D. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
• E. SET XACT_ABORT ON
• F. SET XACT_ABORT OFF

You have three tables that contain data for vendors, customers, and agents. You create a view that is used to look up telephone numbers for these companies. The view has the following definition: You need to ensure that users can update only the phone numbers by using this view. What should you do?

• A. Create an INSTEAD OF UPDATE trigger on the view.
• B. Create an AFTER UPDATE trigger on the view.
• C. Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.
• D. Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.

You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. Your investigation shows the root cause is a query against a read-only table that has a clustered index. The query returns the following six columns: * One column in its WHERE clause contained in a non-clustered index * Four additional columns * One COUNT (*) column based on a grouping of the four additional columns.
You need to optimize the statement. What should you do?

• A. Add an INCLUDE clause to the index.
• B. Add a columnstore index to cover the query.
• C. Cover the unique clustered index with a columnstore index.
• D. Enable the optimize for ad hoc workloads option.
• E. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.
• F. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.
• G. Add a FORCESEEK hint to the query.
• H. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.
• I. Add a HASH hint to the query.
• J. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.
• K. Add a LOOP hint to the query.
• L. Add a FORCESCAN hint to the Attach query.
• M. Include a SET STATISTICS PROFILE ON statement before you run the query.
• N. Include a SET FORCEPLAN ON statement before you run the query.

You administer all the deployments of Microsoft SQL Server 2012 in your company. A database contains a large product catalog that is updated periodically. You need to be able to send the entire product catalog to all branch offices on a monthly basis. Which configuration should you use?

• A. Two servers configured in different data centers SQL Server Availability Group configured in Synchronous-Commit Availability Mode
  One server configured as an Active Secondary
• B. Two servers configured in different data centers SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
• C. Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance
• D. SQL Server that includes an application database configured to perform snapshot replication
• E. Two servers configured on the same subnet SQL Server Availability Group configured in Synchronous-Commit Availability Mode
• F. Two servers configured in the same data center A primary server configured to perform log-shipping every 10 minutes A backup server configured as a warm standby
• G. SQL Server that includes an application database configured to perform transactional replication
• H. Two servers configured in the same data center SQL Server Availability Group configured in Asynchronous-Commit Availability Mode One server configured as an Active Secondary

You develop a Microsoft SQL Server 2012 database that contains tables named Customers and Orders. The tables are related by a column named CustomerId . You need to create a query that meets the following requirements:
Returns the CustomerName for all customers and the OrderDate for any orders that they have placed.
Results must not include customers who have not placed any orders. Which Transact-SQL query should you use?

• A. SELECT CustomerName, OrderDate FROM Customers JOIN Orders ON Customers.CustomerId = Orders.CustomerId
• B. SELECT CustomerName, OrderDate FROM Customers CROSS JOIN Orders ON Customers.CustomerId = Orders.CustomerId
• C. SELECT CustomerName, OrderDate FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerId
• D. SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CuscomerlD = Orders.CustomerId