Clustered Index or Non clustered index on non primary key column?

In my situation I have a table (well many tables) that are using an identity column as their primary key. They will also contain a unique int column RecordID. 90% of the time RecordID will be used to search for a reco开发者_开发百科rd. The only reason the ID identity column even exists is to keep things consistent across our system. In this case should I drop the clustered index from the ID column and add it to the RecordID column? Then add a non-clustered index to the primary key ID in the rare case it will be used to get a record. Thanks!!

If your queries are singleton seek on RecordID (ie. WHERE RecordID=...) then I wouldn't change it. Is true that a clustered index on it will be faster, but would only be noticeable on really really hot cases.

What I would consider for a change it would be if you have range queries (BETWEEN, < or >). Range scans could benefit more significantly from clustered index, as a non-clustered index may be subject to the index tipping point.

Another thing to consider is if you have sort requirements that could be satisfied by this clustered index (ORDER BY in queries, or GROUP BY, or ranking functions like ROW_NUMBER with ORDER by clause). A clustered index would help these better.