Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Adding columns

2 posts in Windows NT Last posting was on 2000-02-14 16:33:02.0Z
Puvendran Selvaratnam Posted on 2000-02-14 01:22:56.0Z
From: "Puvendran Selvaratnam" <puvendran.selvaratnam@btfinancialgroup.com>
Subject: Adding columns
Date: Mon, 14 Feb 2000 12:22:56 +1100
Lines: 25
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <OrK2Yqod$GA.327@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: aupozx665.btal.com.au 203.10.111.3
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2544
Article PK: 1089998

Hi all,

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
appreciate advise

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
db ?

My preference is to recreate the table but the above approach is taken for
the reasons given earlier.

Thanks

Puvendran


Bret Halford Posted on 2000-02-14 16:33:02.0Z
Message-ID: <38A82E3E.654F431D@sybase.com>
Date: Mon, 14 Feb 2000 09:33:02 -0700
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.5 [en]C-CCK-MCD (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
To: Puvendran Selvaratnam <puvendran.selvaratnam@btfinancialgroup.com>
Subject: Re: Adding columns
References: <OrK2Yqod$GA.327@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 42
NNTP-Posting-Host: bret-pc.sybase.com 157.133.80.211
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2542
Article PK: 1089996


Puvendran Selvaratnam wrote:

> Hi all,
>
> 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
> appreciate advise
>
> 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.
>

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
additional space.

As you populate these columns, you will very likely cause page splits resulting
in more
fragmentation. If there will be a massive population of these columns, you may
wish to
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
row to
the syscolumns table. There is no modification made to the data page structures
of
the table. This is because trailing null fields use no storage space on the
data page,
their null value is stored implicitly.

-bret