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.

Improve this question
3
  • Which DBMS are you talking about? Commented Jan 15, 2018 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. Commented Jan 15, 2018 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. Commented Jan 17, 2018 at 15:51

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.