If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > Database Server Software > Sybase > Alter table --- big table

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
Join Date: Jan 2012
Location: Lisbon
Posts: 106
Alter table --- big table

Hello all,

I'm having an issue here at work.
So the problem is we have a set fo 4 tables that are very big.
Lets take the biggest 125gb.

I have to alter table column from a smalldatetime to a datetime.
My first question is:
Since this is an implicit conversion - no data should be copied, right ?
-- after checking query plan, 1 copy of data then 2 update catalog.

Second question:
I don't have nearly enough space left to preform the previous, so my option is:
1. drop indexes and triggers
2. bcp out
3. alter table
4. bcp in
5. create triggers and Indexes.

The bcpout % of space is more less how much of original table size?

Thanks a lot.
Reply With Quote
  #2 (permalink)  
Registered User
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,471
If you issue just the alter table statememnt, you would only need to drop any indexes that reference that column (or all of them, if the clustered index refers to it). If the table has not had its clustered index rebuilt in quite a while, you may have enough fragmentation built up in the table that you can get away with the straight alter table statement.

I had to do a similar thing a long long time ago, but we did not have any table double up in size as if the system was copying all of the rows to a new table. Some client tools will do that, but the alter table statement does not.
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On