DBA issues in SQL Server - File size growing more.

Scenario based solution:

I have a database which has a huge table compared to its overall size. The database is 60GB and this table is 40GB. It contains random generated codes, about 500M of them and this column is the primary key and the clustered index. New code generation batches are inserting 1-5M codes into the table which causes massive fragmentation and eventually, we end up with DBCC CHECKDB running 6 hours on the database (for reference: it completes in 45 minutes for a 120GB database on the same server). Defragmenting this table is again a huge task, because it eats up all the spare resources we have (or maybe more) and needs even more space on the data disk.

The best solution (or workaround) we found so far is that we’re going to rebuild the clustered index with 80% fill factor. Given that insertions are random and there’s no range scan (apart from maintenance tasks like CHECKDB), I think we’ll waste some space and that’s all. (And this table will increase its proportion, in two years, we’re expecting a 120GB database with a 100GB code table.)

Solution:

1. In this scenario, they are tring to create index or building the index on a column which is generating random ID's. Instead, they can create an identity column with clustered index on that column + Non clustered index on the random number generated column.
2. Placing the fill factor around 80 will resolve this problem with quite apt relief.
3. Why can't we go for partitioning the table or archiving the old data

Cheers,
Venkatesan Prabu .J