When adding new column to table, where is that data stored ?

Question

When adding new column to table, where is that data stored ?

sqld-_-ba

SSCarpal Tunnel

Points: 4365
More actions
January 31, 2018 at 5:28 pm

#343262

When a row exceeds 8060 bytes, a ROW_OVERFLOW_DATA page and pointer are created, to fit the 8KB page size limit.
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, similar to ROW_OVERFLOW_DATA (even if adding new column does not exceed row size over 8060 bytes) ?
Or is the entire table reorganized so the new column 'fits' in the original page ?
February 7, 2018 at 12:27 am

This was removed by the editor as SPAM
February 7, 2018 at 3:25 am

This was removed by the editor as SPAM
February 7, 2018 at 3:26 am

This was removed by the editor as SPAM
February 7, 2018 at 3:28 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The new column has to fit on the original page.
If it's also varchar/nvarchar/varbinary, then it too can have portions of its data sent out to row_overflow pages

And your first sentence isn't exactly true. What happens is that the variable columns can have some/all of their contents put on overflow pages. Those pages are not the same structure as the data page, they're closer to how the MAX data types are stored.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqld-_-ba SSCarpal Tunnel Points: 4365 More actions · Answer 2

>The new column has to fit on the original page.
Does that mean the entre table/leaf node pages have to be re-organised ? That way there is no 'row chaining' like in oracle which causes performance issues due to reading from addition pointer pages, correct?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

There's no such thing as 'row chaining'. A row must fit onto a single page, the only exceptions being that data in varchar, nvarchar & varbinary columns can be partially or completely stored off-page (not good for performance though)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass