First off, let’s remember the difference between clustered & nonclustered indexes
The clustered index is organized by the key columns. It also includes every other column as part of the row structure (ie, it has the entire row).
The nonclustered index is also organized by the key columns. It implicitly includes the clustering key columns (if the table is clustered), or a pointer to the row (if the table’s a heap). If any
INCLUDE columns are explicitly specified, they will also be included in the index structure (but these included columns don’t affect order).
Should I ever have both on the same columns?
There is a corner case where it makes sense to have a non-clustered index “duplicating” the clustered index. If you have a query that frequently scans the table, and ONLY makes use of the clustering key column, the query optimizer will prefer to use the non-clustered index. The non-clustered index does not contain the full row data, and thus it will take up less physical space. Because it takes up less physical space, SQL Server can scan the table with fewer IOs, and will make use of it for performance reasons.
I’ll also note that SQL Server will tend to make the same decision on a seek: If two indexes are both covering, it will choose to use the one that takes up the least space.
You can query
sys.dm_db_index_usage_stats, you can see the number of
user_scans on the two indexes. This will help you see how the two indexes are being used, and determine the usefulness of the two indexes.
OK, I don’t have that corner case, which one should I drop?
As a general rule, most tables should have a clustered index. (That’s a whole other topic). Without knowing any details about your index usage or data access, I’d guess that if you were to drop one, you would drop the nonclustered index.
Depending on the clustering key, the data, the data access patterns, etc., the most correct answer might be to drop the clustered index and create a different clustered index. You may want to read more on Effective Clustered Indexes.