Microsoft 070-457 Value Pack (Frequently Bought Together)

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

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 ARITHABORT ON
• B. BEGIN
• C. SET XACT_ABORT ON
• D. TRY
• E. SET XACT_ABORT OFF
• F. SET ARITHABORT OFF

You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01. You need to write messages to the Application Log when users are added to or removed from a fixed server role in Server01. What should you create?

• A. a Server Audit Specification
• B. a Policy
• C. an Alert
• D. a Database Audit Specification
• E. an Extended Event session
• F. a SQL Profiler Trace
• G. a Resource Pool

You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products. You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data. You need to ensure that the following requirements are met:
Future modifications to the table definition will not affect the applications' ability to access data.
The new object can accommodate data retrieval and data modification.
You need to achieve this goal by using the minimum amount of changes to the existing applications.
What should you create for each application?

• A. table partitions
• B. views
• C. table-valued functions
• D. stored procedures

You are a database developer of a Microsoft SQL Server 2012 database. The database contains a table named Customers that has the following definition:

You need to ensure that the CustomerId column in the Orders table contains only values that exist in the CustomerId column of the Customer table. Which Transact-SQL statement should you use?

• A. ALTER TABLE Orders ADD CONSTRAINT PK_Orders_CustomerID PRIMARY KEY (CustomerID)
• B. ALTER TABLE Orders ADD CONSTRAINT CK_Orders_CustomerID CHECK (CustomerID IN (SELECT CustomerId FROM Customer))
• C. ALTER TABLE Customer ADD OrderID INT NOT NULL;
  ALTER TABLE Customer ADD CONSTRAINT FK_Customer_OrderID FOREIGN KEY (OrderID) REFERENCES Orders (OrderID);
• D. ALTER TABLE Customer ADD CONSTRAINT FK_Customer_CustomerID FOREIGN KEY (CustomerID) REFERENCES
  Orders (CustomerID)
• E. ALTER TABLE Orders ADD CONSTRAINT FK_Orders_CustomerID FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID)