Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Dec 2003
    Location
    Bangalore
    Posts
    28

    Unanswered: Changing datatype: smallint to int in a large table..

    Hi,

    I need to change the datatype of a very large table from smallint to int...
    What would be an ideal solution to get this done in least amount of time. May be I can try with ALTER but , I am not sure about the time it would take ...and the page splits etc..

    pls help on the same!!

    Thanks
    Cheriyan.

  2. #2
    Join Date
    Oct 2003
    Posts
    357
    Hi,
    If the table has thousands of records,definitely it will take some time to alter the datatype and in the mean time make sure that no application is running which is interacting with that table.

    Madhivanan

  3. #3
    Join Date
    Dec 2003
    Location
    Bangalore
    Posts
    28
    Thanks Madhivanan

    Is there any way to get around this...any alternate solution with would reduce the total time required..

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Not really. You need to do the ALTER TABLE. Dropping any indexes that hit this column would speed it up.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    rdjabarov would suggest that you BCP the data out, create a new table, and then BCP the data back in. It's not something I've done a lot, but its worth a shot.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    How can I alter my settings in dbForums so that I can be notified when an answer that I would have come up with and is known to everybody can be sent to me as if I posted it, and at the same time increase MY postings counter?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Go to the dbforums/forumstore page and select a 1 point item and I will get it and have it sent to you. They have some new stuff in, so check it out.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I checked. All they have left is gag balls and gimp masks in gift wrappers. Give me your address I'll send you a set
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oh, that's nasty.
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    rdjabarov would suggest that you BCP the data out, create a new table, and then BCP the data back in. It's not something I've done a lot, but its worth a shot.

    Are you kidding...that's the only way to FLY!

    bcp out...need to do it in CHAR format,drop table, recreate with new types, and bcp back in...
    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
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Out of curiosity, how big is the table (i.e. number of rows)? I am not sure where the turning point is for the performance of BCP vs. just issuing the command. Naturally for a table of 10 rows, you would just issue the alter table command. For a table of 10,000,000 rows, you would probably bcp. Has anyone experimented with the two methods to see where the turnover point is?

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I agree...however....the more paractice the better, especially with less data...the screw ups won't take as long to undo.....

    But yes, 10 would be silly
    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.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, I found the exact number of rows where BCP becomes more efficient, but I have to let rdjabarov tell you. I will tell you, however, that it is neither a prime number, palindromic, or a perfect square.
    If it's not practically useful, then it's practically useless.

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

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Who cares what the number is, it's how long your mgmt will allow you to keep users from production, that's the determinant. Besides, it depends on what field you ALTER (change, add, or remove), so you (blindman) don't really KNOW the exact number, alright?!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Oh, brother.....
    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.

Posting Permissions

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