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).