Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312

    Unanswered: Adding Fields To A Table

    I am new to SQL Server 2005 and I am trying to add two fields to an existing table. The table has 15 Million records in it and the save is not completing. How do I add the new fields?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How are you doing it now?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Through the Management Studio interface. Modify the table, add the fields, save the table. I get a timeout expired message.

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    are you adding them to the end of the table, or inserting between 2 other fields, the latter taking a longer time.

    look at Alter Table...

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Did you save the script?

    And are you moving the columns into places not that are the last

    It will make a temp copy of the table, copy all of the rows, rebuild the new table, then copy the data over, then do an sp_rename, then drop the original

    Lot of overhead

    just do this

    Code:
    CREATE TABLE myTable99(col1 int IDENTITY(1,1), Col2 datetime DEFAULT(GetDate()), Col3 Char(1))
    GO
    
    INSERT INTO myTable99(Col3)
    SELECT 'a' UNION ALL SELECT 'b' UNION ALL SELECT 'c'
    GO
    
    SELECT * FROM myTable99
    GO
    
    ALTER TABLE myTable99 ADD Col4 binary
    GO
    
    SELECT * FROM myTable99
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I am adding the fields to a specific place in the table (not at the end). Why does this matter? I tried adding to the end and it saved fine. What do I need to do to add the fields where I would like them to be in the table. Some existing processes rely on the order of the fields.

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    see the last line in Bretts signature. this violates relational theory. your application should not act like this.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    The reason we have it this way is to simplify our archival processes. By using the field indexes instead of field names the code does not have to change when the physical structure of the tables changes as long as both tables have the same structure.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The problem you are facing is that Management studio is going to do the following to accomplish this:

    1) Create a new table with the name tmp_yourtablename with all the columns in the order you want.
    2) Transfer all of the data from the old table to the tmp_ table. Yes. all 15 million rows will be doubled up.
    3) Drop the old table (usually with no error checking)
    4) Rename the tmp_table as the original table name.

    Contrast that with the alter table command which would append the two new columns on the end of the table, and takes a few seconds to run.

    How can the archival process be simpler by using the field index? i would think that any of the columns "pushed out" by the insertion of new fields in the "middle" of the table to cause much larger problems.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by DCKunkle
    Some existing processes rely on the order of the fields.

    Why would that be?

    SELECT * perhaps?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by DCKunkle
    The reason we have it this way is to simplify our archival processes. By using the field indexes instead of field names the code does not have to change when the physical structure of the tables changes as long as both tables have the same structure.

    OK, so what does that have to do with not adding them to the end?

    In any case, The logging is what's going to kill you

    I might bcp the data out
    CREATE the new table
    bcp the data back in, using a format card, into the new table
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    The archival is being done in Visual Basic, using ADO recordsets. When you reference the table's Fields collection using the index, as long as field 1 is ID in one table and 1 is ID in the other table then it doesn't matter the names of the fields, but the order is important.

  13. #13
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    The problem with adding them to the end is, the only difference in the table structure is the last field in the archive table. It is the datetime the record was archived.

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by DCKunkle
    The archival is being done in Visual Basic, using ADO recordsets.

    Shoot me now
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    How many indexes are on this table? You might consider dropping them before you add the new columns, and then recreating them.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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