Hello! I have a table (the table is simplified to show the specific issue) CREATE TABLE Messages( FromUserId INT, ToUserId INT Topic VARCHAR(100) ) Now I would like to discuss the best index in our situation. The table will contain about 2-3 million rows. If I create a clustered index on (FromUserId, ToUserId), then when we make a new insert we will force the table to be resorted since we cannot know the order in which FromUserId that is sending a message, and most likely it will not be in order. Therefore since the clustered index needs to be sorted it will probably be alot of sorting and page splits and that sort of things. But if I add a "dummy" identity-column CREATE TABLE Messages( MessageId INT IDENTITY(1,1), FromUserId INT, ToUserId INT Topic VARCHAR(100) ) And create one clustered index over (MessageId) and then one non-clustered index on (FromUserId, ToUserId). Then we dont need to have page splits and resorts on the actual data, but instead there will be some movement on the index. Which will probably save us some on the performance. But on the other hand we will have two indexes that needs to be updated. The table has alot of activity, booth reads and writes. (selects (90%), inserts (10%)) Maybe the best thing would be to change the fillfactor? What are your thoughts about this?
Given that SELECT:INSERT ratio, I would go for two separate indices. What about UPDATEs? Can they happen?