Alter Table....Add Column

Question

Alter Table....Add Column

Arsh

SSCertifiable

Points: 6354
More actions
September 29, 2017 at 6:31 am

#340414

Hey Folks,
Question about alter table. When we add a column(with/without some default values) to an existing table , does SQL accommodate the space in the last available pages of the database or does it cause page splits and fill them immediately after the last pages for the last column on the table ?

Viewing 10 posts - 1 through 9 (of 9 total)

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

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

A row cannot be stored across multiple pages (LOB data types notwithstanding). When you add a column, the data has to be added to each row.

Given that, what do you conclude happens?

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

ScottPletcher SSC Guru Points: 101131 More actions · Answer 2

There's a huge difference between with and without a default.

If there's a default value, SQL has to modify metadata and add write that default value to every row of every page that contains the new column. That often causes page splits and, thus, a very long time to add a column.

If there's no default value, SQL has to modify metadata but only needs minor changes to data pages, usually not requiring page splits. Obviously that's a much faster process.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

ScottPletcher - Friday, September 29, 2017 7:49 AM
There's a huge difference between with and without a default.

Only for variable width data types, surely? For fixed width data types, a NULL takes up the same space as a default (or specified) value.

John

Arsh SSCertifiable Points: 6354 More actions · Answer 4

GilaMonster - Friday, September 29, 2017 6:37 AM
A row cannot be stored across multiple pages (LOB data types notwithstanding). When you add a column, the data has to be added to each row.
Given that, what do you conclude happens?

I wonder how a row length cannot be more than 8KB ? Text type is understandable as its Out-of-row but what about In-row VARCHAR(MAX) ?

Arsh SSCertifiable Points: 6354 More actions · Answer 5

John Mitchell-245523 - Friday, September 29, 2017 8:00 AM
ScottPletcher - Friday, September 29, 2017 7:49 AM
There's a huge difference between with and without a default.
Only for variable width data types, surely? For fixed width data types, a NULL takes up the same space as a default (or specified) value.
John

I c John . Thanks for putting the difference.

Arsh SSCertifiable Points: 6354 More actions · Answer 6

ScottPletcher - Friday, September 29, 2017 7:49 AM
There's a huge difference between with and without a default.
If there's a default value, SQL has to modify metadata and add write that default value to every row of every page that contains the new column. That often causes page splits and, thus, a very long time to add a column.
If there's no default value, SQL has to modify metadata but only needs minor changes to data pages, usually not requiring page splits. Obviously that's a much faster process.

Thanks Scott. Yeah I understand that if there's no default value it depends on the size of the column for that row. So understandably as indicated by Gail , the new column values are added to the same page where the rest of the columns exist .That means there are so many things happening there if the table is large...... Thank u.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Arsh - Friday, September 29, 2017 8:35 AM
GilaMonster - Friday, September 29, 2017 6:37 AM
A row cannot be stored across multiple pages (LOB data types notwithstanding). When you add a column, the data has to be added to each row.
Given that, what do you conclude happens?
I wonder how a row length cannot be more than 8KB ? Text type is understandable as its Out-of-row but what about In-row VARCHAR(MAX) ?

http://sqlhints.com/2013/03/10/difference-between-sql-server-varchar-and-varcharmax-data-type/

John

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

Arsh - Friday, September 29, 2017 8:35 AM
I wonder how a row length cannot be more than 8KB ? Text type is understandable as its Out-of-row but what about In-row VARCHAR(MAX) ?

Varchar(max) is not in-row. It's the replacement for TEXT.

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

ScottPletcher SSC Guru Points: 101131 More actions · Answer 9

GilaMonster - Friday, September 29, 2017 2:53 PM
Arsh - Friday, September 29, 2017 8:35 AM
I wonder how a row length cannot be more than 8KB ? Text type is understandable as its Out-of-row but what about In-row VARCHAR(MAX) ?
Varchar(max) is not in-row. It's the replacement for TEXT.

According to MS docs (not always accurate), varchar(max) data is by default stored in the row if the size allows it. You'd have to explicitly set 'large values types out of row' to 1 to force all values to be stored outside the row. Until I know differently, I assume the docs are correct, and set the "overflow limit" if/as needed, table by table. I'd recommend doing that anyway -- if it's only, say, 20 bytes, do you really want to store a 16-byte pointer and have to do another, non-sequential I/O to go retrieve the original 20 bytes??

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".