I have a SQL Server database that has a pretty important table (join wise) that contains just over 2.1 million rows. For the
VARCHAR column that defines the clustered index, 17.5k rows contain unique values, the remaining rows all contain exactly the same non null value. There are some queries that start at this table restricting by this column, expecting to get just one row, so that probably helps performance, but for many queries we are accessing via other fields and nearly always joining by a unique, non nullable (
BIGINT) primary key (which is obviously non clustered).
I guess my question is, in light of anything else is it a no-brainer to change the clustered index to be the primary key (in light of there not being a more suitable candidate). Is there any check that I could do to try and predict the outcome (spinning up an exact replica and testing in a real world way is not an option)
I know that any changes I make will require a table rebuild (if I'm changing the clustered key)