Post reply

Adding a column in middle of a table

  • December 20, 2007 at 9:38 pm

    #180017

    Is it possible in SQL Server 2005, to add a column in middle of the table or as first column through query.

    Please help.

    Iam having a table with 10 columns, and i need to add a new column as a first column or as a 2nd column.how to do. i need a query for this.

    Also i cannot drop the table.

  • December 20, 2007 at 10:09 pm

    #761978

    Possible, yes. Recommended, not in my book. In fact, unless you come up with a VERY good reason for this, I can't in good conscience tell you how to do so, as it involves mucking around with system tables.

    So, since everyone else is going to ask anyway, we might as well get it out of the way first. Why does the ordinal position of the columns matter so much?

    Now, with that said, if it really is a requirement, I'd discuss the issue with the person who is dictating that the table can't be dropped, and explain why you need to drop the table. The recommended way to do this is to create a new table, dump the data into it, verify that everything is fine, drop the old table, and rename the newly created one to replace the old. You'll have to recreate any indexes, constraints, triggers, etc. Or, you could do it the easy way, and let SSMS do it for you, which will do the same thing behind the scenes, but with less work on your part.

    Still, the best solution is to eliminate any code that cares about the ordinal positions of the columns.

  • December 20, 2007 at 11:33 pm

    #761981

    Yes thats right.But i thought there may be an simple query to do that.

    I dont want to use system tables to alter.

    But i have heard in MySQl that this is possible using AFTER Command.

    Anyway its ok

  • December 21, 2007 at 2:40 am

    #762016

    Another option is to use a VIEW. Create a view from the original table and select the columns in the required order and forget about the table. Use the VIEW for SELECT, INSERT, UPDATE, DELETE operations.

    .

  • December 21, 2007 at 6:49 am

    #762087

    Seriously, why would physical column order matter? You can always get the columns out in any order that you want through views, procedures and functions.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • December 21, 2007 at 7:29 am

    #762108

    There would never be any problem if you enumerate columns when ever you do an Insert or a Select.

    We did a good thing at our company. We made it as rule that all SQL Select or Insert should have column list enuerated or else it will not go to production:D

    -Roy

  • December 21, 2007 at 7:53 am

    #762121

    Hear! Hear! We've invoked similar rules.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 21, 2007 at 8:05 am

    #762129

    We do that too!

    .

  • December 21, 2007 at 8:13 am

    #762131

    Yeah, that's part of our best practices check list too.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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

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