1

I've inherited a project and I'm seeing something pretty interesting that I haven't come across in the past. Looking for guidance.

The Primary Key on a table is a Unique, Non-Clustered Index and there is also a Unique Clustered Index on same column.

Is there any situation where you'd want this, or should I make alterations so that the primary key is a unique, clustered index?

Thanks in advance.

  • Which DBMS are you talking about? – a_horse_with_no_name Jan 15 '18 at 22:26
  • Speaking for SQL Server, you usually see this when someone created a nonclustered PK on a table (the default is clustered), and then later ran into trouble with the table being a Heap so they added a clustered index over it. It's not typically the kind of thing you'd wanna mess with unless it's causing issues. – Erik Darling Jan 15 '18 at 22:34
  • Thanks all. It's SQL Server. We're thinking that it was changed up this way to move the clustered index to a filegroup on another drive. Several tables with the same schema. I appreciate the feedback. – Jmark Jan 17 '18 at 15:51
2

In SQL Server the PK index will, by default, be a clustered index, containing all of the data rows at the leaf level of the index.

Queries of the form

SELECT COUNT(*) FROM T

or

SELECT ... FROM T ORDER BY ID OFFSET 100 ROWS FETCH NEXT 10 ROWS ONLY

Would normally use the clustered index. But scanning the whole clustered index to count the rows, or to fetch a page of rows from deep in the sort order is expensive.

So it's a separate non-clustered index on the same key is sometimes created to support these queries.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.