Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    114

    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.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,554
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •