Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2006
    Posts
    7

    Unanswered: About ordering of columns

    i add new column using alter command but i always found it in the end of table but
    i want to add it in particular position between the columns...........

    how to do so .............

  2. #2
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    The only way to do that is dropping the table and recreating it with the columns in the position you want.

    But why do you want to do this? Column order has no significant meaning in the database and can be arbitrarily changed in the the statements you use.

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I think if you use EM or SSMS to add a column you can add a column to the middle as well, but this likely does just what lexiflex suggests under the covers.

    I agree with lexiflex: why do you care? The only reason I can think of is you are using "select *" in your app code, which is a no-no.

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You CAN move columns around in EM when in table design view. Just drag
    the column to the position you want, and save the design.

    ummmm... I would NEVER do this, I just saw somebody else do it (heh heh)
    Inspiration Through Fermentation

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by RedNeckGeek
    You CAN move columns around in EM when in table design view. Just drag the column to the position you want, and save the design.
    but under the covers, EM creates a new table, copies the data from your original table, and then drops it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Quote Originally Posted by r937
    EM creates a new table, copies the data from your original table, and then drops it
    Which brings me to another reason for not using EM and making scripts yourself...

    Try adding a NOT NULL-column without a default somewhere in between the other columns. Running the EM-script will cause an error about NULL-values in NOT NULL-columns just before it drops your original table and renames the temp-table. Thus making all the data from that table magically disappear...

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by RedNeckGeek
    You CAN move columns around in EM when in table design view. Just drag
    the column to the position you want, and save the design.

    ummmm... I would NEVER do this, I just saw somebody else do it (heh heh)
    I freely admit to doing this all the time.
    Why? Because I like my data columns in logical groups, thats why.
    Quote Originally Posted by Lexiflex
    But why do you want to do this? Column order has no significant meaning in the database and can be arbitrarily changed in the the statements you use.
    Absolutely correct. The database does not care about column order. But PEOPLE do....
    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
  •