I have an MS SQL Server 2008 R2 database that contains a table that has just under 100 million rows & is both read and write heavy.
It contains Tweets, and the id of each Tweet is one of the fields, but it is not used as the primary key, instead it has a non-clustered index on it & there is a separate field for the primary key "localPkId" (also the clustered index) that is just an identity and isn't actually used anywhere (so all other tables will still join on the Tweet ID, ignoring the "localPkId" field).
The Tweets that get inserted will come into the system out of order, so by not using Tweet ID as the clustered index, it means new rows will always get put at the end, speeding up inserts.
This had been working fine for some time as the table had a predominantly write heavy workload, but now it's being used for a lot of reads as well and I'm concerned that as the workload continues to grow this will become a problem.
First question: Does having "localPkId" offer any advantage over the table being a heap/having no clustered index?
Second question: Should the Tweet ID be the Primary Key & Clustered Index & then set a low fillfactor? If so, how would I go about choosing an appropriate value for the fillfactor in a case like this?