question

This seems like a follow up to this question: https://ask.sqlservercentral.com/questions/141399/clustered-index-on-identy-column.html although slightly different. 1. Yes, the clustered index should help with the performance of the daily process as that's what you are using to locate records. I would compare execution plans to ensure that you are getting a clustered index seek versus a scan. I would not create a non-clustered index for the identity column since any queries with that in the WHERE clause should utilize the clustered index. Regarding the performance of the weekly process, you might see some performance gains as the new records would be written at the "bottom" of the table since the key is on the identity. Note though, that if you have additional non-clustered indexes in play, those have to be updated as well upon any DML process so it might offset things. Again, I would suggest that you test things out as your mileage may very. 2. The clustered index is the table itself. It will most likely take up more space since it will contain the actual data of the column. The non-clustered indexes just holds pointers to the data within the clustered index so while still taking up space, it should be less than the actual table itself. Be aware that creating the clustered index on the table with 50 million rows will take time and could cause blocking. I suggest you do some testing on the best process to create this index. Also, create the clustered index first before creating any non-clustered indexes. This will help to prevent the non-clustered indexes from being rebuilt. Hope that helps!