adding columns to a large table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

adding columns to a large table

Author

Topic

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-08-05 : 17:24:48

I have a table that has 6 billion (10^9) rows in it, and need to add a 5 new columns. The columns being added are all nullable.

My understanding of adding nullable columns is that this shouldn't take hours and hours since no new extents need to be allocated or new pages written, since all the columns being added are nullable. If they weren't nullable, and had defaults, then it would be another story.

Is this correct? Or can I expect these ALTERs to take hours and hours?

Here's the current schema, with all the columns renamed to satisfy my paranoid bosses:


CREATE TABLE BigOne
(
    col1 int not null,
    col2 int not null,
    col3 tinyint not null,
    col4 tinyint not null,
    col5 smallint null,
    col6 tinyint not null,
    col7 tinyint null,
    col8 tinyint null,
    col9 tinyint null,
    col10 decimal(4,3),
    col11 tinyint null,
    col12 tinyint null,
    col13 decimal(4,3),
    col14 tinyint null,
    col15 smallint null, 
    col16 decimal(4,3),
    col17 tinyint null,
    col18 smallint null, 
    col19 decimal(4,3) null,
    constraint PK_BigOne primary key (col1 asc, col2 asc)
)

and here are the columns I need to add:


alter table [dbo].[BigOne] add col20 int null
alter table [dbo].[BigOne] add col21 int null
alter table [dbo].[BigOne] add col22 int null
alter table [dbo].[BigOne] add col23 smallint null
alter table [dbo].[BigOne] add col24 decimal(5,3) null

elsasoft.org

pootle_flump

1064 Posts

Posted - 2008-08-06 : 06:39:18

Hi Jesse

AFAIK this should be instantaneous - these are not added to each data page (which I assume is what you are afeared of). I am 95% sure of this but will look up a source.

pootle_flump

1064 Posts

Posted - 2008-08-06 : 06:47:39

It was Kalen's book I remembered reading this and a quick search led to:
http://msdn.microsoft.com/en-us/library/aa224807(SQL.80).aspx
vwalah!

pootle_flump

1064 Posts

Posted - 2008-08-06 : 06:52:22

Tested empirically - confirmalised.

BTW - your column names look a bit dodgy - are you aware of first normal form?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-06 : 07:19:32

hmm.... but you're trying to add int's.
AFAIK the page in 2k5 is build so that the fixed size columns are first then come the variable length columns.
also data in nullable fixed size columns is datatype internal default value (0 for int, decimals, etc) but with a null bitmap set.
so it still has to set the value and reshufle the pages.
No?

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-08-06 : 07:32:47

Rows that didn't move didn't reflect the space for the new column until SQL Server received an UPDATE for the row.

pootle_flump

1064 Posts

Posted - 2008-08-06 : 08:14:01

Retested. Even if the number of columns added means that a new NULL bitmap byte is required there is still no writing to the datapages.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-08-06 : 10:00:26

thanks guys!

elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-08-06 : 10:03:18

quote:
Originally posted by pootle_flump

BTW - your column names look a bit dodgy - are you aware of first normal form?

I generally use only 13th abnormal form.

elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-08-06 : 12:05:23

quote:
Originally posted by Kalen Delaney - http://msdn.microsoft.com/en-us/library/aa224807.aspx

SQL Server changes metadata only when you add a new column to a table without specifying a default value (the new column must allow NULLs in that case)

yay!

elsasoft.org

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-06 : 13:04:29

pootle, how exactly did you test this?

so if i understand all this correctly the data gets reshuffled only after an update or insert happens to that page?

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

pootle_flump

1064 Posts

Posted - 2008-08-07 : 04:12:44

I tested on a table with 40 million rows. One clustered identity PK (fill factor 100), 7 nullable (but populated) columns. Adding a couple of fixed width, nullable columns took a few milliseconds. Statistics IO was on and returned nothing, presumably because only system data was written. A couple of COUNT(*)s took about 30 seconds. I was going to get fancy and compare DBCC PAGE before and after but, frankly, it didn't seem necessary after the above. Quite clearly there were NO changes to the data pages.

I am curious though about the NULL bitmap though. It should have required a further byte to account for the 9th nullable column. Why does this not need to be written to the data pages?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources