Unique, Non-Clustered Index and Unique Clustered Index on Same Column

Question

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.

• user1822: Which DBMS are you talking about?
• Erik Reasonable Rates Darling: 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.
• Jmark: 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.

Answer 1

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.