79322365

Date: 2025-01-01 21:47:06
Score: 1
Natty:
Report link

Creating a unique index on the same column as a clustered primary key column is unnecessary. Creating a clustered primary key on a table automatically creates a coinciding unique clustered index on the table. See the “Primary and foreign key constraints” article for reference. Adding an additional unique, non-clustered index to the table adds to the table’s data storage. The additional index also adds an additional to your table maintenance and index maintenance work.

sys.key_constraints and sys.indexes query

Once you drop the duplicative unique index, IX_Products_1, your query should utilize the clustered index on the primary key. Even if you leave in the unique index on the table, the query optimizer automatically builds the most efficient query plan it can estimate based on your query. It is not guaranteed that the optimizer will choose the unique index over the clustered index, or vice versa. See the “Logical and physical showplan operator reference” article for a listing of operations used by the query optimizer.

Reasons:
  • Long answer (-0.5):
  • No code block (0.5):
  • Low reputation (1):
Posted by: Ezekiel Kaufman