0

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

3
  • @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.
    – Shackle Shot
    Sep 12, 2023 at 17:45
  • 2
    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
    Sep 12, 2023 at 17:46
  • @DanGuzman thanks, I did what you said and now the increase in data space is lowered to 50%. Thanks again my friend.
    – Shackle Shot
    Sep 12, 2023 at 17:58

1 Answer 1

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.