I have a table with 125 columns, and 250 million rows. Unfortunately the ID column is NVARCHAR(15), and is non-unique. The table undergoes 000's of updates and inserts every day. The clustered index is on fields which assist the reads, however it rapidly gets fragmented and inserts are slow.
I'm attempting to fix the clustered index, and like the idea of partitioning the table...
I considered placing the clustered index on ID & InsertedLogID to make it unique, and would also support partitioning on ranges of InsertedLogID. However 34 (potential) bytes fails the 'Narrow' key objective.
Then I began to think a new Identity column may be a better choice, as it achieves the objective of Ever-Increasing, and reduces page splits. However partitioning on this Identity column wouldn't be very useful.
Could I make the clustered index a compound index of Identity and InsertedLogID columns, and still achieve the Ever-Increasing objective?
Also does UNIQUE need to be specified in the clustered index declaration so SQL knows the key is unique (and doesn't add the uniquifier)?
Thanks in advance,