Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2007-09-30
Medium Priority
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 29

Accepted Solution

Nightman earned 2000 total points
ID: 19988186
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

ID: 19988622
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
ID: 19989817

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

ID: 19990630

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.


Author Comment

ID: 19991524
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.


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

581 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