Why does adding a new UniqueIdentifier column to my existing SQL Server table increases data space of the table by 100%?

Question

I have an existing table and once I add an ObjectId UniqueIdentifier column with default NewSequentialId() value, the data space suddenly doubles. I'm using the script below:

ALTER TABLE Employee
ADD ObjectId uniqueidentifier constraint Employee_ObjectId 
 default(newsequentialid()) not null 

For example, if I have currently 3MB of data space used, with 23k records, and execute the above script, suddenly it becomes almost 6MB of data space.

My objective here is to learn the reason why this is happening, because I imagine if there are millions of records, this will consume too much storage space after.

The original script was using newid() instead of newsequentialid(). Found some info regarding fragmentation when using newid(), so I replaced that. But other than that I'm at lost here.

Employee Table

Id (PK, bigint, not null)
InfoA (nvarchar(4000), null)
InfoB (numeric(28,9), null)
Company (FK, bigint, not null)

Index: 1 Clustered index (PK) and 2 Non-unique non-clustered index

• Shackle Shot: @MartinSmith added my table columns. Data space can be seen when I go to the properties of the table and then to the "storage" tab.
• Dan Guzman: Every page in the table will need to be split to add the new column with the default value, bloating space. Reorg the clustered index afterwards to reclaim space.
• Shackle Shot: @DanGuzman thanks, I did what you said and now the increase in data space is lowered to 50%. Thanks again my friend.

Answer 1

Every page in the table will need to be split to add the new column with the default value, bloating space. Reorg the clustered index afterwards to reclaim space. – Dan Guzman