2

I am trying to check how the table size is going to be increased after adding new column with default values.

I use the following default values: NULL, ''(empty string) and some text(some random text)` but it seems the table size is not changed.

I am using the following statements:

EXEC sp_spaceused '[Table_A]';

ALTER TABLE [dbo].[Table_A]
ADD [Loops] NVARCHAR(900)
CONSTRAINT [DF_Loops] DEFAULT('XXX') WITH VALUES;

EXEC sp_spaceused '[Table_A]';

ALTER TABLE [dbo].[Table_A]
DROP CONSTRAINT [DF_Loops]

ALTER TABLE [dbo].[Table_A]
DROP COLUMN [Loops];

but the sp_spaceused always returns:

name                                               rows                 reserved           data               index_size         unused
------------------------------------------------- -------------------- ------------------ ------------------ ------------------ ------------------
Table_A                                            73540994             8701688 KB         6658544 KB         2042976 KB         168 KB

Could anyone tell what I am doing wrong?

Paul White
  • 94,921
  • 30
  • 437
  • 687
gotqn
  • 4,348
  • 11
  • 52
  • 91

1 Answers1

9

You've added a new column, but (the Enterprise Edition of) SQL Server - if the default value is a runtime constant - will not update the existing pages that hold the table's data, it will be only a metadata change and that's the reason you see no increase in the space used.

For all intents and purposes (as Aaron mentioned in a comment), the values are there - and a SELECT will reveal them.

Think of it as SQL-Server black magic. It manages to store information in 0 bytes. Remus Rusanu has a nice blog post about this optimization and more details about what (magic) happens in the background and when you subsequently update the column:
Online non-NULL with values column add in SQL Server 2012

The basic info about it, in MSDN docs, ALTER TABLE:

Adding NOT NULL Columns as an Online Operation

Starting with SQL Server 2012 Enterprise Edition, adding a NOT NULL column with a default value is an online operation when the default value is a runtime constant. This means that the operation is completed almost instantaneously regardless of the number of rows in the table. This is because the existing rows in the table are not updated during the operation; instead, the default value is stored only in the metadata of the table and the value is looked up as needed in queries that access these rows. This behavior is automatic; no additional syntax is required to implement the online operation beyond the ADD column syntax. A runtime constant is an expression that produces the same value at runtime for each row in the table regardless of its determinism. For example, the constant expression 'My temporary data', or the system function GETUTCDATETIME() are runtime constants. In contrast, the functions NEWID() or NEWSEQUENTIALID() are not runtime constants because a unique value is produced for each row in the table.

ypercubeᵀᴹ
  • 99,450
  • 13
  • 217
  • 306