SQL Server – Table Size Unchanged After Adding New Column

alter-tablesize;sql serversql server 2014

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?

Best Answer

You've added a new column, but 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.

Related Solutions

SQL set allowed values for a column

alter table ExampleTable
    add (new_column varchar(20) default 'value1',
         constraint ckExampleTable check (new_column in ('value1', 'value2', 'value3')));

Postgresql – Altering a parent table in Postgresql 8.4 breaks child table defaults

Your problem is that when you add a new column to the_person, its child, the_person_two will have this field appended at the end of columns list (4th position), so after has_default column. See:

db=> \d temp_person
  Column   |       Type        |                            Modifiers                            
-----------+-------------------+-----------------------------------------------------------------
 person_id | integer           | not null default nextval('temp_person_person_id_seq'::regclass)
 name      | character varying | 
 foo       | text              | 

db=> \d temp_person_two 
   Column    |         Type         |                            Modifiers                            
-------------+----------------------+-----------------------------------------------------------------
 person_id   | integer              | not null default nextval('temp_person_person_id_seq'::regclass)
 name        | character varying    | 
 has_default | character varying(4) | not null default 'en'::character varying
 foo         | text                 |

So, when you execute this:

INSERT INTO temp_person_two VALUES ( NEW.* );

PostgreSQL will actually understand that you want to insert on the first three columns of temp_person_two (as NEW.* will expand to three values), generating something similar to this:

INSERT INTO temp_person_two(person_id,name,has_default)
VALUES ( NEW.person_id, NEW.name, NEW.foo );

So, temp_person_two.has_default will get the value of NEW.foo, which is NULL in your case.

The solution is to simply expand the column names:

INSERT INTO temp_person_two(person_id,name,foo)
VALUES ( NEW.person_id, NEW.name, NEW.foo );

or, you could also use this:

INSERT INTO temp_person_two(person_id,name,foo)
VALUES ( NEW.* );

But this is weak, as any changes on column positions may break your statements, so I'd recommend the first one.

EDIT:

So the conclusion and the lesson learned here is:

Always explicitly type the names of the columns and the values when issuing an INSERT command, in fact, when issuing any SQL command at all... =D

This will save you a lot of time solving problems like that in future.

Best Answer

Related Solutions

SQL set allowed values for a column

Postgresql – Altering a parent table in Postgresql 8.4 breaks child table defaults

Related Question