Multiple choice technology databases

An existing sales catalog database structure exists on a system in your company. The company sells inventory from a single warehouse location that is across town from where the computer systems are located. The product table has been created with a nonclustered index, based on the product ID, which is also the primary key. Nonclustered indexes exist on the product category column and also the storage location column. Most of the reporting done is ordered by storage location. How would you change the existing index structure?

  1. Change the definition of the primary key so that it is a clustered index.

  2. Create a new clustered index, based on the combination of storage location and product category.

  3. Change the definition of the product category so that it is a clustered index.

  4. Change the definition of the storage location so that it is a clustered index.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Because most of the reporting is ordered by storage location, setting it as the clustered index physically sorts the table data by this column, optimizing query performance. The primary key on product ID can be changed to nonclustered.

Keep practicing — related questions