You are developing a database reporting solution for a table that contains 900 million rows and is 103 GB.
The table is updated thousands of times a day, but data is not deleted.
The SELECT statements vary in the number of columns used and the amount of rows retrieved.
You need to reduce the amount of time it takes to retrieve data from the table. The must prevent data duplication.
Which indexing strategy should you use?

• A. a hash index for the table
• B. a clustered columnstore index for the table
• C. a clustered index for the table and nonclustered indexes for nonkey columns
• D. a nonclustered index for each column in the table

Note: This question is part of a series of questions that present the same scenario. Each question in this series contains a unique solution. Determine whether the solution meets the stated goals.
You are developing a new application that uses a stored procedure. The stored procedure inserts thousands of records as a single batch into the Employees table.
Users report that the application response time has worsened since the stored procedure was updated. You examine disk-related performance counters for the Microsoft SQL Server instance and observe several high values that include a disk performance issue. You examine wait statistics and observe an unusually high WRITELOG value.
You need to improve the application response time.
Solution: You add a unique clustered index to the Employees table.
Does the solution meet the goal?

• A. Yes
• B. No

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a Microsoft SQL Server database named DB1 that contains the tables described in the following table:

There are no foreign key relationships between TBL1 and TBL2.
You need to minimize the amount of time required for queries that use data from TBL1 and TBL2 to return data.
What should you do?

• A. Drop existing indexes on TBL1 and then create a clustered columnstore index. Create a nonclustered columnstore index on TBL1 Make no changes to TBL2.
• B. Create a nonclustered index on tbl2 only.
• C. Create a clustered index on TBL1. Create a nonclustered index on TBL2 and add the most frequently queried columns as included columns.
• D. Create clustered indexes on TBL1 and TBL2.
• E. Drop existing indexes on TBL1 and then create a clustered columnstore index. Create a nonclustered columnstore index on TBL1. Create noncluster index on TBL2.
• F. Create unique constraints on both TBL1 and TBL2.Create a partitioned view that combines columns from TBL1 and TBL2.

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a database named DB1. There is no memory-optimized filegroup in the database.
You have a table and a stored procedure that were created by running the following Transact-SQL statements:

The Employee table is persisted on disk. You add 2,000 records to the Employee table.
You need to create an index that meets the following requirements:
- Optimizes the performance of the stored procedure.
- Covers all the columns required from the Employee table.
- Uses FirstName and LastName as included columns.
- Minimizes index storage size and index key size.
What should you do?

• A. Create a nonclustered index on the table.
• B. Create a clustered columnstore index on the table.
• C. Create a clustered index on the table.
• D. Create a hash index on the table.
• E. Create a nonclustered filtered index on the table.
• F. Create a nonclustered columnstore index on the table.

You have the following stored procedure:

The Numbers table becomes unavailable when you run the stored procedure. The stored procedure obtains an exclusive lock on the table and does not release the lock.
What are two possible ways to resolve the issue? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.

• A. Set the ANSI_DEFAULT statement to OFF and add a COMMIT TRANSACTION statement after the INSERT statement.
• B. Add a COMMIT TRANSACTION statement after the INSERT statement.
• C. Remove the SET ANSI DEFAULTS ON statement.
• D. Remove the implicit transaction and the SET ANSI_DEFAULTS ON statement.