What should you do?

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 os 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 run the following query:

The following image displays the execution plan the query optimizer generates for this query:

Users frequently run the same query with different values for the local variable @lastName. The table named Person is persisted on disk. You need to create an index on the Person.Person table that meets the following requirements:

All users must be able to benefit from the index.

FirstName must be added to the index as an included column.

What should you do?
A . Create a clustered index on the table.
B . Create a nonclustered index on the table.
C . Create a nonclustered filtered index on the table.
D . Create a clustered columnstore index on the table.
E . Create a nonclustered columnstore index on the table.
F . Create a hash index on the table.

View Answer

Answer: B

Explanation:

By including nonkey columns, you can create nonclustered indexes that cover more queries. This is because the nonkeycolumns have the following benefits: They can be data types not allowed as index key columns. They are not considered by the Database Engine when calculating the number of index key columns or index key size.