I have been working on indexing a database that deals with historical data and recently found a problem with a clustered index that surprised me.
The database is SQL Server 2018. The table itself deals with the account information and is essentially a fact table, it is completed with around 200k records per day and has 200 columns. It is currently in 68 thousand records, and before the index occupied about 120 GB of space.
Because it is a historical table about the account information, and we do not have UUID configured (which would be a great effort that my boss told me not to devote my time to this database), I created the primary key + clustered index in the Date field and account number with an extract equivalent to:
ALTER TABLE AcctHistory ADD CONSTRAINT PK_AcctHistory PRIMARY KEY CLUSTERED (Date, Account_Number)
The Date field is a Date and time field (This is a DEV environment that I did not configure but I am now building. I only know that I plan to update it to a date soon, but I provide the type if it is relevant.), The Account_Number field It is a bigint.
After executing this declaration, according to the SSMS disk space report per table, the amount of space consumed by the table skyrocketed up to 480 GB, 4 times what it took before the index.
I know that indexes take up space, but I've never heard of anything like that. Is this normal or is it indicative of a problem that I can solve to save space?