SQL Server data file creates 50% unallocated bloat when I create clustered index

Question

My company has relatively large datasets with clustered indexes currently being built on a char(40) field. In order to maintain disk space and streamlining backups, I currently have a "one table per database" build plan.

I am able to properly size the database for the size of our data, but when we create our CIX, even though it only consists of 5% of the database size, the data file bloats 45-50%. My syntax for generating these alerts is standard, posted below.

CREATE CLUSTERED INDEX [CIX_column_name] ON [dbo].[table_name]
(
    [column_name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

I regret not capturing the report before adding the index, but I can confidentially say it was > 99% purple, denoting the space being taken by data itself. I have my auto-growth settings at 2GB, set intentionally small to try and limit the bloat.

Data size is 119 GB and some change.

I am now interested in seeing if there is a technique to build these indexes without the data file bloating, as I certainly do not want to get into a habit of shrinking/rebuilding the database. My only thoughts currently are to exploit SORT_IN_TEMPDB, but I tested on a small dataset and did not see the results I was hoping for.

I run SQL Server 2019 (15.0.2095.3), Standard Edition (64 bit).

Answer 1

data size is 119 GB and some change

If your data is ~120 GB in a heap and you're creating a clustered index, you'll need space to hold the new clustered index and space to hold the heap data which means you'll need somewhere above 2x the space for this which would put you around 240GB. Once the creation is done, the heap will be deallocated, leaving you with ~120GB of free space.

There is more than goes into this than just that, and some options change the behaviors. You can read more about the operations you're executing, what they do and require, here.