We are adding a few columns to large tables we have which are around the
10GB mark. As a result of space issues and time taken, it was decided to do
this via "alter table...add column..". I have concernes about this and would
1. Will adding columns at the end be a performance overhead when we try to
access these columns e.g will there
be hops to other pages when these new columns are accessed, something
similar to overflow pages.
2. What will be the effect on the transaction log as the columns are added
to the tables. Is it recording page restructuring (adding/ dropping) in the
My preference is to recreate the table but the above approach is taken for
the reasons given earlier.
Subject: Adding columns
Date: Mon, 14 Feb 2000 12:22:56 +1100
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
NNTP-Posting-Host: aupozx665.btal.com.au 22.214.171.124
Xref: forums-1-dub sybase.public.sqlserver.nt:2544
Article PK: 1089998
Date: Mon, 14 Feb 2000 09:33:02 -0700
From: Bret Halford <firstname.lastname@example.org>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.5 [en]C-CCK-MCD (WinNT; I)
To: Puvendran Selvaratnam <email@example.com>
Subject: Re: Adding columns
Content-Type: text/plain; charset=us-ascii
NNTP-Posting-Host: bret-pc.sybase.com 126.96.36.199
Xref: forums-1-dub sybase.public.sqlserver.nt:2542
Article PK: 1089996
No. By using "alter table...add column", the columns you are adding must be
nullable, and they are created with a default value null and take up no
As you populate these columns, you will very likely cause page splits resulting
fragmentation. If there will be a massive population of these columns, you may
schedule a drop/recreate of the clustered index (or a REORG in 11.9.2 or higher)
afterwards to remove the fragmentation. With the exception of TEXT/IMAGE,
ASE always stores complete rows on pages.
> 2. What will be the effect on the transaction log as the columns are added
> to the tables. Is it recording page restructuring (adding/ dropping) in the
> db ?
There will be very little logging activity for this.
Adding a nullable column to a table involves little more than adding a single
the syscolumns table. There is no modification made to the data page structures
the table. This is because trailing null fields use no storage space on the
their null value is stored implicitly.