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 TRANSACTION ISOLATION LEVEL SNAPSHOT
• B. SET IMPLICIT_TRANSACTIONS ON
• C. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
• D. SET IMPLICIT_TRANSACTIONS OFF
• E. SET XACT_ABORT OFF
• F. SET XACT_ABORT ON

You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person.
The tables have the following definitions:

Users are able to use single INSERT statements or INSERT...SELECT statements into this view.
You need to ensure that users are able to use a single statement to insert records into both Employee and Person tables by using the VwEmployee view.
Which Transact-SQL statement should you use?

• A. CREATE TRIGGER TrgVwEmployee
  ON VwEmployee
  INSTEAD OF INSERT
  AS
  BEGIN
  DECLARE @ID INT, @FirstName NVARCHAR(25), @LastName NVARCHAR(25), @PersonID INT, @EmployeeNumber NVARCHAR(15) SELECT @ID = ID, @FirstName = FirstName, @LastName = LastName, @EmployeeNumber
  = EmployeeNumber
  FROM inserted
  INSERT INTO Person(Id, FirstName, LastName)
  VALUES(@ID, @FirstName, @LastName)
  INSERT INTO Employee(PersonID, EmployeeNumber)
  VALUES(@PersonID, @EmployeeNumber
  End
• B. CREATE TRIGGER TrgVwEmployee
  ON VwEmployee
  INSTEAD OF INSERT
  AS
  BEGIN
  INSERT INTO Person(Id, FirstName, LastName)
  SELECT Id, FirstName, LastName, FROM inserted
  INSERT INTO Employee(PersonId, EmployeeNumber)
  SELECT Id, EmployeeNumber FROM inserted
  END
• C. CREATE TRIGGER TrgVwEmployee
  ON VwEmployee
  INSTEAD OF INSERT
  AS
  BEGIN
  INSERT INTO Person(Id, FirstName, LastName)
  SELECT Id, FirstName, LastName FROM VwEmployee
  INSERT INTO Employee(PersonID, EmployeeNumber)
  SELECT Id, EmployeeNumber FROM VwEmployee
  End
• D. CREATE TRIGGER TrgVwEmployee
  ON VwEmployee
  FOR INSERT
  AS
  BEGIN
  INSERT INTO Person(Id, FirstName, LastName)
  SELECT Id, FirstName, LastName, FROM inserted
  INSERT INTO Employee(PersonId, EmployeeNumber)
  SELECT Id, EmployeeNumber FROM inserted
  END

You are developing a database application by using Microsoft SQL Server 2012.
An application that uses a database begins to run slowly.
You discover that during reads, the transaction experiences blocking from concurrent updates.
You need to ensure that throughout the transaction the data maintains the original version.
What should you do?

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

You use a Microsoft SQL Server 2012 database.
You need to create an indexed view within the database for a report that displays Customer Name and the total revenue for that customer.
Which four T-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)