HOTSPOT
You use SQL Server 2014. You create a table within a database by using the following DDL:

The following table illustrates a representative sample of data:

The system is expected to handle 50 million orders a month over the next five years.
You have been instructed by your Team Lead to follow best practices for storage and performance in the utilization of SPARSE columns.
Which columns should you designate as SPARSE? To answer, mark each column as SPARSE or NOT SPARSE in the answer area.

You have a SQL Server 2012 database named Database1. You execute the following code:

You insert 3 million rows into Sales.
You need to reduce the amount of time it takes to execute Proc1.
What should you do?

• A. Run the following:
  DROP INDEX IX_Sales_OrderDate;
  GO
  CREATE INDEX IX_Sales_OrderDate ON Sales(OrderDate);
  GO
• B. Change the WHERE clause to the following: WHERE OrderDate BETWEEN
  CAST(@date1,char(10))
  AND CAST(@date2,char(10))
• C. Run the following: ALTER TABLE Sales ALTER COLUMN OrderDate datetime NOT
  NULL;
• D. Remove the ORDER BY clause from the stored procedure.

You need to recommend a solution to allow application users to perform UPDATE operations on the database tables. The solution must meet the business requirements.
What should you recommend?

• A. Create a Policy-Based Management Policy.
• B. Create functions that use EXECUTE AS clauses.
• C. Create stored procedures that use EXECUTE AS clauses.
• D. Create a user-defined database role and add users to the role.

You need to implement changes to the system to reduce contention and improve performance of the SalesOrderDetail table.
Which three actions should you perform? Each correct answer presents part of the solution. Choose three.

• A. Use (SNAPSHOT] hints in the update statements
• B. ALTER DATABASE [ProdDB] SET READ_COMMITTED_SNAPSHOT OFF
• C. Use (TABLOCK) hints in the report queries
• D. Use (SNAPSHOT] hints in the report queries
• E. SET TRANSACTION ISOLATION LEVEL SNAPSHOT
• F. ALTER DATABASE [ProdDB] SET ALLOW.SNAPSHOT ISOLATION ON
• G. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
• H. ALTER DATABASE [ProdDB] SET READ_COMMITTED_SNAPSHOT ON

You have a database named DB1.
You plan to create a stored procedure that will insert rows into three different tables. Each insert must use the same identifying value for each table, but the value must increase from one invocation of the stored procedure to the next.
Occasionally, the identifying value must be reset to its initial value.
You need to design a mechanism to hold the identifying values for the stored procedure to use.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.

• A. Create a fourth table that holds the next value in the sequence. At the end each transaction, update the value by using the stored procedure. Reset the value as needed by using an UPDATE statement.
• B. Create a sequence object that holds the next value in the sequence. Retrieve the next value by using the stored procedure. Increment the sequence object to the next value by using an ALTER SEQUENCE statement. Reset the value as needed by using a different ALTER SEQUENCE statement.
• C. Create an identity column in each of the three tables. Use the same seed and the same increment for each table. Insert new rows into the tables by using the stored procedure. Use the DBCC CHECKIDENT command to reset the columns as needed.
• D. Create a sequence object that holds the next value in the sequence. Retrieve the next value by using the stored procedure. Reset the value by using an ALTER SEQUENCE statement as needed.