Specifying position when adding a column

  • Hi guys, do you know a way to add a column specifying its position in the table? Any

    T-SQL or SQL-DMO solution would be nice.

    Olivier

  • You can do it in EM. Other than that you can do it yourself by bcp'ing the data to a temp table, altering the table to be the way you want, bcp back in. Generally recommended not to worry about column order, you can always use a view to customize it.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • Alternately, create new table with columns like you want them, INSERT SELECT all the rows into the new table, drop the old table, sp_rename the new. That's essentially what Enterprise Manager does (run profiler on it when using it to see some neat tricks). But if you're looking for a simple way to say "put the new column between the existing second and third columns" in code, then there's not really a way to do that in one simple step.

    - Troy King


    - Troy King

  • The only problem with that last trick is you will lose constriant, indexes, permissions, etc. EM scripts out all that stuff for you in addition. However I agree on the one fact. The column position only matters when looking at the physical table or doing a SELECT *, which really is a personal preference. Other than that when you actually query you can order the column output in your query how you want.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • Keep in mind that the tenets behind the "theoretical underpinnings" of relational database systems say the columns and rows can be in any order.... as a result, if specific order is needed, this is done on the retrieval.

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

  • ok, guys, it's just that I needed to do add a column in the first position for like 100 tables, so I would have liked to be able to generate a script automatically or use SQL-DMO. My tables have a lot of columns and I'm trying to keep them in a logical order for easier maintenance. I know how EM does it in the background but it's pretty heavy, especially if I want to do it for 100 tables with a lot of constraints on it.

    I'll do it manually with the good old EM.

    Thanks.

    Oliv'

  • Is there a reason the column order is important?

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

  • Like I said in my last post, I like to order columns in a business logical order, but primarily I had 100 tables that I needed to use with replication, so I wanted to add primary keys to them and I prefer primary keys in the first position...

    Oliv'

    quote:


    Is there a reason the column order is important?

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1


Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply