You are a database developer. You plan to design a database solution by using SQL Server 2008.
A database contains a table named Person. The structure of the table is as shown below:
The table has the following indexes:
• A unique clustered index on the PersonID column named IX_Person_PersonID
• A nonclustered index on the FirstName and LastName columns named IX_Person_FirstName_LastName
• A nonclustered index on the PersonType column named IX_Person_PersonType that has FirstName and LastName as included columns
The table contains approximately 700,000 records. The approximate number of records for each PersonType is 3,000
You execute the following query.
SELECT P.FirstName, P.LastName
FROM Person P
WHERE P.PersonType = ‘DR’
You plan to analyze the performance of the query by using an execution plan. You need to ascertain that the indexes are used optimally.
What should you do?
A. Verify that a clustered index scan operation is performed on the IX_Person_PersonID index.
B. Verify that an index seek operation is performed on the IX_Person_PersonType index, and a key lookup operation is performed on the IX_Person_PersonID index.
C. Verify that an index seek operation is performed on the IX_Person_PersonType index.
D. Verify that an index seek operation is performed on the IX_Person_PersonType index, and an index scan operation is performed on the IX_Person_FirstName_LastName index.
