adding a column to a hugh table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DelphiDeveloper - 30 Jul 2008 21:43 GMT

I will need to add a column to a customers database table. The table has
records more then two years old abd has approx. 10,000 records. I have never
been able to add a column to a table with much less records. I will be doing
so with SqlWb unless there is a better way. We discussed truncating the
table to creating a new one going forward but they want total continuity of
their data.

What method is the best way to add a column to a very large database?
Thanks

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 30 Jul 2008 22:06 GMT

Yes, there is a better way. First, 10K is by no means "huge". You should
use an ALTER TABLE command in a query window. This will have much less
overhead than what SSMS will likely do behind the scenes "for" you.

ALTER TABLE dbo.TableName ADD column_name data_type;

At this point you will have a NULLable column. You can change your stored
procedures / applications to start populating it going forward, but if you
need to do anything else first depends on what you want to do with the rows
that already exist. Should that column be populated? With what?

On 7/30/08 4:43 PM, in article
17F85A07-CB20-451A-A7DC-8D7F7765070F@microsoft.com, "DelphiDeveloper"

> I will need to add a column to a customers database table. The table has
> records more then two years old abd has approx. 10,000 records. I have never
[quoted text clipped - 5 lines]
> What method is the best way to add a column to a very large database?
> Thanks

Reply to this Message

DelphiDeveloper - 30 Jul 2008 23:32 GMT

Thanks Aaron,
I meant to say 10 million. I had logged in via my PDA and mis-typed. I
will try as you say and if that fails, I will create a new modified table and
write a small app to pump the data over to it, drop the original and rename
the new table to the old one.

If you see any problems with this method, other then the length of time to
pump the data (I will do this over a weekend), please let me know.

Thanks

> Yes, there is a better way. First, 10K is by no means "huge". You should
> use an ALTER TABLE command in a query window. This will have much less
[quoted text clipped - 19 lines]
> > What method is the best way to add a column to a very large database?
> > Thanks

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 31 Jul 2008 00:25 GMT

> Thanks Aaron,
> I meant to say 10 million.

Well, that's bigger, but still not huge. :-)

Still, may require a slightly different approach, unless the application(s)
can be offline.

> will try as you say and if that fails, I will create a new modified table and
> write a small app to pump the data over to it,

Why would you use an application for this? You want the app to load 10MM
rows and then write them to a new table? Or worse, migrate each row one at
a time? Why incur all this extra overhead moving the data from the database
to the app and then back to the database again?

If you can temporarily block writing to the large table (e.g. by taking the
apps offline, or at least by redirecting inserts to a new table temporarily,
if you can block updates but not inserts), you can do:

SELECT *, new_column = CONVERT(data_type, NULL)
INTO dbo.NewTable
FROM dbo.OldTable;

When done, add any indexes and constraints. Then do your renames, update
the new column if you need to, and add constraints on the new column. If
you temporarily collected new data in another table, you can copy it over
after you are done.

Reply to this Message

Tibor Karaszi - 31 Jul 2008 09:29 GMT

An important aspect to look for in Aaron posts is that some of these operations can be meta-data
only operations. This means that adding the column will be instantaneous. Now, don't use the GUI for
this, since the GUI will likely do something silly instead of a straight-forward ALTER TABLE ...
ADD. And test first, so you in advanced know if your particular action is meta-data only or not.
Things that affect whether this is meta-data only can include such as existing table layout
(possibly including indexes keys etc), datatype of the column to add, nullability, default value
etc.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

>I will need to add a column to a customers database table. The table has
> records more then two years old abd has approx. 10,000 records. I have never
[quoted text clipped - 5 lines]
> What method is the best way to add a column to a very large database?
> Thanks

Reply to this Message

Eric Russell - 31 Jul 2008 16:04 GMT

Altering the table to add a new column, defaulting as NULL, will generally
take only a second, even on a very large table, and it should not change the
total number of pages.

However, if you follow that up with a mass update to populate the new column
with a value, then that operation could take several minutes or even longer.
Also, depending on the table's FILLFACTOR and how much page splitting is
caused by changing the length of every row in the table, you may be left with
fragmentation and significantly (maybe even double) the amount of pages
(reserved space).

You will want to experiment on a dev or staging server before offering any
guarantees to your users about what impact it will have on uptime.

For example, I performed 2 tests on a table with 9 million rows and a
clustered index.
In the first test, after reindexing with a fill factor of 90%, adding a new
column of type int, and then updating it to 0, it did not impact the total
number of pages or fragmentation percentage.

FILLFACTOR 90%

DBCC DBREINDEX ('My9millionRowTable', '', 90);

sum_page_count    avg_record_size_in_bytes    sum_fragment_count    avg_fragmentation_in_percent    avg_page_space_used_in_percent
60657    32    985    4.1    69.6

ALTER TABLE My9millionRowTable ADD newcolumn int null;

sum_page_count    avg_record_size_in_bytes    sum_fragment_count    avg_fragmentation_in_percent    avg_page_space_used_in_percent
60657    32    985    4.1    69.6

UPDATE My9millionRowTable SET newcolumn = 0;

sum_page_count    avg_record_size_in_bytes    sum_fragment_count    avg_fragmentation_in_percent    avg_page_space_used_in_percent
60657    33    985    4.1    71.9

However, in the 2nd test, after reindexing with a fill factor of 100%,
adding a new column of type int, and then updating it to 0, the total number
of pages and fragmentation percentage doubled.

FILLFACTOR 100%

DBCC DBREINDEX ('My9millionRowTable', '', 100);

sum_page_count    avg_record_size_in_bytes    sum_fragment_count    avg_fragmentation_in_percent    avg_page_space_used_in_percent
54950    32    917    4.1    72.8

ALTER TABLE My9millionRowTable ADD newcolumn int null;

sum_page_count    avg_record_size_in_bytes    sum_fragment_count    avg_fragmentation_in_percent    avg_page_space_used_in_percent
54950    32    917    4.1    72.8

UPDATE My9millionRowTable SET newcolumn = 0;

sum_page_count    avg_record_size_in_bytes    sum_fragment_count    avg_fragmentation_in_percent    avg_page_space_used_in_percent
109993    33    109913    56    61.4

Here is the query used for table usage statistics:

SELECT
    sum(ps.page_count) as sum_page_count,
    avg(ps.max_record_size_in_bytes) as avg_record_size_in_bytes,
    sum(ps.fragment_count) as sum_fragment_count,
    round(avg(avg_fragmentation_in_percent),1,4) as avg_fragmentation_in_percent,
    round(avg(ps.avg_page_space_used_in_percent),1,4) as
avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'DETAILED')
as ps
    left join sys.indexes as i
        ON i.object_id = ps.object_id AND
            i.index_id = ps.index_id
where
    object_name(ps.object_id) = 'My9millionRowTable'
group by
    object_name(ps.object_id)

Reply to this Message