Clustered Index taking huge amount of space

by Josh Kraushaar   Last Updated August 14, 2019 00:06 AM - source

I have been working on indexing a database dealing with historical data and recently encountered an issue with a clustered index that surprised me.

The database is SQL Server 2018. The table itself deals with account information and is essentially a fact table, it is populated with around 200k records a day and has 200 columns. Currently it is sitting at 68mil records, and before the index it took up around 120GB of space.

Because it is a historical table on account information, and we do not have UUID's set up (which would be a massive effort my boss told me not to spend my time on with this database), I created the primary key + clustered index on the Date field and Account Number with a statement equivalent to:

ALTER TABLE AcctHistory 

The Date field is a Datetime field (This is a DEV environment that I didn't set up but am now building out. Just know I plan on updating it to a date soon, but I am providing the type in case that is relevant.), the Account_Number field is a bigint.

After running this statement, according to the SSMS disk space by table report, the amount of space the table consumed shot up to 480GB, 4x what it took before the index.

I know indexes take up space, but I have never heard of anything like this. Is this normal or is this indicative of a problem I can solve to save space?

Related Questions

Create a clustered primary key on partitioned table

Updated March 14, 2019 12:06 PM

Clustered Index Secured Views and FK questions

Updated August 21, 2018 06:06 AM