'Row Chaining/Overflow' in SQL Server when adding column to table?

Question

Each row in a table can be 8060 bytes, since page size is 8KB. If a row size exceeds this, the largest column is moved to another page ROW_OVERFLOW_DATA and a pointer is created on the original page.

What happens when a new column is added to an existing table ? Is the data stored at the 'end' of the table, and pointers are created after each existing row in a page to point to this new data ? Clearly this would cause performance issues..

Or is the entire table reorganized so the new column 'fits' in the original page ?

We're running SQL 2012 (& above) Enterprise, but also interested to hear about lower versions. I'm interested in both heaps & clustered indexes, because in either one, the existing row must be modified - to add the new column, or to add a pointer to the new column, right ?

Answer 1

There are three separate concepts here and I think that it's important to keep them separate.

1. Adding a column to a table

Adding a column to a table is an optimized operation designed to not take a lot of time. Most of the time it will not require all pages in the table to be modified. You can verify by this by measuring the size of the transaction required to do so or by looking at the space used by the table before and after.

Suppose that you add a column to a table with a default value of 1. The table has 100 billion rows. Does it really make sense to modify every data page to write a "1" for every row? That could take a really long time. Instead, you can interpret the data in a way to work around it. If a value for the new row hasn't been written to the page yet then you can assume that it's the default value.

Martin Smith pointed out that this optimization only apply to Enterprise edition. One rule of thumb is that SQL Server needs to modify every page if the new value for the column isn't constant, such as a computed column or a column with a default of NEWID(). There are edge cases which may result in all pages getting modified, such as this one.

2. Updating a column for an existing row

If you update a column it's possible that the full row will no longer fit on the data page. For heaps, the row will be moved to a new page with a pointer. This is known as forwarded records. For clustered indexes some of the rows will be moved to a new page. This is known as a page split and many people have written extensively about them. If a table has so many forwarded records and page splits that you're experiencing some kind of performance problem then that can be cleaned up with a REBUILD. But you probably have better things to worry about.

3. Rows larger than 8k

As of SQL Server 2008 rows can be larger than 8k. Some of the mechanics involved in doing that are explained in the link that you have in the question. Conceptually this really has very little to do with adding a column to a table.

Answer 2

I want to elaborate more on Martin Smith's comment:

JO: (1) Adding a column to a table is an optimized operation designed to not take a lot of time. Most of the time it will not require all pages in the table to be modified.

MS: Your. (1) is only available on Enterprise Edition from 2012+. And only if the default is a runtime constant. Otherwise it is not an online operation and does indeed modify every row.

First, a small cite from Microsoft SQL Server 2012 Internals By Kalen Delaney, Craig Freeman

Adding a new column

You can add a new column, with or without specifying column-level constraints. If the new column doesn’t allow NULLs, isn’t an identity column, and isn’t a rowversion (or timestamp column), the new column must have a default constraint defined (unless no data is in the table yet). SQL Server populates the new column in every row with a NULL, the appropriate identity or rowversion value, or the specified default. If the newly added column is nullable and has a default constraint, the existing rows of the table aren’t filled with the default value, but rather with NULL values. You can override this restriction by using the WITH VALUES clause so that the existing rows of the table are filled with the specified default value.

Note In SQL Server 2012 Enterprise Edition, adding a NOT NULL column with a default value is performed as a metadata-only operation when the default value is a constant. The existing rows in the table aren’t updated during the operation; instead, SQL Server stores the default value in the metadata of the table and accesses the value as needed during query processing.

In fact, if we try to add not null column with the constant default value in SQL Server 2012, it will be metadata-only operation.

Here is my repro.

==============================================================

2012, adding of the not null default with constant value

I create a table with the rows of 2000 bytes so that only 4 rows fit in 1 page:

create table dbo.t2000(
    id int not null,
    filler varchar(1971) not null
); 

insert into dbo.t2000 values
(1, replicate('1', 1971)), (2, replicate('2', 1971)), (3, replicate('3', 1971)), (4, replicate('4', 1971));

dbcc ind(db1, t2000, 1);
-- PagePID PageType
-- 11916   10 (iam)
-- 11915   1  (In-row data) 

I used dbcc ind to verify that my table has only 2 pages, 1 IAM page + 1 data page. Every row has the size of exactly 2000 bytes.

Now I add 360 bytes to every row and check if the table will grow:

alter table dbo.t2000 add col1 char(360) not null default (replicate(convert(char(36),'char_36'),(10)));

dbcc ind(db1, t2000, 1);
-- PagePID PageType
-- 11916   10 (iam)
-- 11915   1  (In-row data) 


dbcc traceon (3604);
dbcc page (db1, 1, 11915, 3);

--Slot 0 Offset 0x60 Length 2000
--Record Type = PRIMARY_RECORD        
--Record Size = 2000                  

--Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
--Slot 0 Column 2 Offset 0xf Length 1971 Length (physical) 1971
--Slot 0 Column 3 Offset 0x0 Length 360 Length (physical) 0

We can see that our table still has only 1 data page and every row of this page still has 2000 characters (in the commented part of the code I output how every slot looks like and even if Column 3 has 360 bytes they are not physical bytes (this part I want to show as a picture: we clearly see our 360 bytes composed of 10 replicate of 'char_36' casted to fixed char(36)

My second repro adds

====================================================================

not null column but with non-constant default in SQL Server 2012

I recreate my table with 4 2000-bytes rows:

if object_id('dbo.t2000') is not null drop table dbo.t2000;
go

create table dbo.t2000(
    id int not null,
    filler varchar(1971) not null
); 

insert into dbo.t2000 values
(1, replicate('1', 1971)), (2, replicate('2', 1971)), (3, replicate('3', 1971)), (4, replicate('4', 1971));

dbcc ind(db1, t2000, 1);
-- PagePID PageType
-- 11916   10 (iam)
-- 11915   1  (In-row data) 

Now add not null column with non-constant default:

alter table dbo.t2000 add col1 char(360) not null default (replicate(convert(char(36),newid()),(10)));

dbcc ind(db1, t2000, 1);
-- PagePID PageType
-- 11916   10 (iam)
-- 11915   1  (In-row data) 
-- 11919   1  (In-row data)

Note that now we do have additional data page. It's because now our default non-costant value is physically stored, so every row no more has only 2000 bytes, now we have 2360-bytes rows. Below is shown how 11915 page looks like:

dbcc traceon (3604);
dbcc page (db1, 1, 11915, 3);


--Slot 0 Offset 0x60 Length 9
--Record Type = FORWARDING_STUB       Record Attributes =                 Record Size = 9
--Forwarding to  =  file 1 page 11919 slot 0 

--Slot 1 Offset 0x69 Length 2360
--Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFO
--Record Size = 2360 

--Slot 2 Offset 0x9a1 Length 2360
--Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFO
--Record Size = 2360

--Slot 3 Offset 0x12d9 Length 2360
--Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFO
--Record Size = 2360                  

So we see that our Slot 0 was moved to 11919 page and we have only a small forwarding record of 9 bytes that shows the new location of our Slot 0

And now my 3d test on pre-2012 server:

====================================================================

Adding not nul column with constant default value on SQL Server 2005

We are on Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) Dec 10 2010 10:56:29 Copyright (c) 1988-2005 Microsoft Corporation Express Edition with Advanced Services on Windows NT 6.2 (Build 9200: )

I create the same table and have the same 2 pages, IAM + data page:

if object_id('dbo.t2000') is not null drop table dbo.t2000;
go

create table dbo.t2000(
    id int not null,
    filler varchar(1971) not null
); 

insert into dbo.t2000 
select 1, replicate('1', 1971)
union ALL
select 2, replicate('2', 1971)
union ALL
select 3, replicate('3', 1971)
union ALL
select 4, replicate('4', 1971);

dbcc ind(db1, t2000, 1);
-- PagePID PageType
-- 80   10 (iam)
-- 73   1  (In-row data) 

Now I add a column with a constant value (seems as it coud be metadata-only operation? Not so if we are on pre-2012 instance!!!)

alter table dbo.t2000 add col1 char(360) not null default (replicate(convert(char(36),'char_36'),(10)));
dbcc ind(db1, t2000, 1);
-- PagePID PageType
-- 80   10 (iam)
-- 73   1  (In-row data) 
-- 89   1  (In-row data)

Now we have 2 data pages, 73 + 89, let's explore our 73-page:

dbcc traceon (3604);
dbcc page (db1, 1, 73, 3);

We see the same thing as we sow in 2012 when adding non-constant default: the first slot was moved to another page leaving only a 9-bytes forwarding record that indicate its new location:

==========================

CONCLUSION

Adding a not null column is not a metadata-only operation unless you are on server with version >= 2012 and new column has a constant default.