Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2003
    Posts
    114

    Unanswered: Switch Column Order

    Is there anyway to switch the physical order of columns in a sql server db? I want column 1 to become column 2 and vice versa. The columns are already populated and I don't want to lose data.

    Thanks!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes. You can rearrange columns easily in Enterprise Manager's table design form. In addition, before saving your changes you can click the script button near the left of the button bar and it will show you the SQL script it will run in order to make your changes. It generally involves creating a new empty table with your changes applied, copying data from the original table into the new table, deleting the original table, and then renaming the new table. It will also drop and recreate necessary keys, indexes, constraints, etc...

    blindman

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by blindman
    Yes. You can rearrange columns easily in Enterprise Manager's table design form. In addition, before saving your changes you can click the script button near the left of the button bar and it will show you the SQL script it will run in order to make your changes. It generally involves creating a new empty table with your changes applied, copying data from the original table into the new table, deleting the original table, and then renaming the new table. It will also drop and recreate necessary keys, indexes, constraints, etc...

    blindman
    If your table is big (100000 rows or more depends on size of row and configuration of your server - memory, tempdb, hard drives, etc.) - EM sometimes cannot save your changes (becuase of big transaction). I am using DTS (without transaction) instead of insert (see script from EM). Also, if your there is no space for copy of table in database - you have move a table to another database and then copy data to new table.
    Last edited by snail; 10-06-03 at 07:55.

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    You could also leave the table as it is, rename it and create a view to define the column order.

    But the column order within a table should not matter to you. It is defined for physical storage by syscolumns.coloffset and for presentation by colid but if you always access the table by naming the columns then it shouldn't make any diffence what these values are.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, I guess the unasked question is, why is it important to you to switch the column order? Using SELECT * is poor programming practice. It makes debugging difficult and is not as efficient if you don't actually need all the columns.

    blindman

  6. #6
    Join Date
    Apr 2003
    Posts
    114
    This has all been very helpful. Thanks! Yes, the problem is that I still use SELECT * some (I didn't know that it was considered poor programming style). I did switch the column order by using a drag-n-drop technique by pausing the mouse over the column in question and then dragging it to its new location.

    But again, thanks for the help! And you have 'cured' me of a 'bad' programming technique.

Posting Permissions

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