How can I add a column in the first ordinal position?

Posted on 2007-09-30
Last Modified: 2008-01-09
I have a table without an id field.

I know i can add an id field using the command

alter table <table> add ID int identity primary key

That's fine, and it works Ok. This line adds the ID colum in the last (rightmost) position in the table.

Purely for aesthetic reasons (I'm so fussy) I would like if possible to be able to add the column to the leftmost position instead. Is there a way I can specify the ordinal position that a column can be added to?
Question by:molar
    LVL 30

    Accepted Solution

    You can only do that by creating a temporary table (with the correct schema), importing the data from the old table (insert into), dropping the existing table and then renaming the temp table.

    If you use the editor in Enterprise Manager and generate script instead of saving the table, you will see the T-SQL code that it generates for you (of course, if you save it Enterprise Manager will execute this script for you).
    LVL 15

    Expert Comment

    If you use EM it will do it for you automatically if you save the table changes. Just go into design mode, add the column, then select the column and drag it up to the top of the list. When you save the changes, EM will change the column order.
    LVL 25

    Expert Comment

    by:Lee Savidge

    In Enterprise Manager, when you edit the table to add a column, don't add it to the bottom of the list. Right mouse click on the column you wish to make your new column appear before and choose insert new column. It will add it in the required place.


    LVL 15

    Expert Comment


    Actually, either way will work. You can add the column to the bottom of the list and then drag it to the position you want it saved. Until you actually save the changes, no change is made to the DDL, SQL Server is just building the DDL in memory and does not execute anything until you save.

    LVL 2

    Author Comment

    Hi Guys. Thanks for the responses.
    I was really looking for a way to do in SQL. I was aware I could do it in EM (though i didn't know about the option to save changes as a SQL script).

    I'm a bit surprised it's quite so fiddly. Still there it is.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now