12
ALTER TABLE Log ADD log_id bigint IDENTITY BEFORE cust_id_fk

The above code adds a new column to last position. I want it to be added to the first position. Also I want to make it as Primary Key.

Akhil K Nambiar
  • 3,835
  • 13
  • 47
  • 85
  • 2
    The "order" of columns in a table *really* shouldn't matter - their physical storage order is likely different from what you see anyway. – Damien_The_Unbeliever Oct 26 '11 at 08:02
  • 1
    It makes **no** difference except to your OCD. [One](http://stackoverflow.com/questions/6121884/sql-server-2008-cannot-insert-new-column-in-the-middle-position-and-change-dat/6121952#6121952) and [Two](http://stackoverflow.com/questions/6692021/performance-space-implications-when-ordering-sql-server-columns/6692107#6692107). FYI @Damien_The_Unbeliever some info to back you up – gbn Oct 26 '11 at 09:12
  • 2
    I want my columns to look pretty. – Chloe Jul 07 '13 at 19:39

7 Answers7

10

You would need to drop the table and recreate it with the columns in the correct order. If you make the table changes in SSMS then it can generate a change script for you which you could then use to deploy the change to a production server.

Ira Rainey
  • 5,173
  • 2
  • 34
  • 42
3

Even if the question is old, a more accurate about Management Studio would be required.

You can create the column manually or with Management Studio. But Management Studio will require to recreate the table and will result in a time out if you have too much data in it already, avoid unless the table is light.

To change the order of the columns you simply need to move them around in Management Studio. This should not require (Exceptions most likely exists) that Management Studio to recreate the table since it most likely change the ordination of the columns in the table definitions.

I've done it this way on numerous occasion with tables that I could not add columns with the GUI because of the data in them. Then moved the columns around with the GUI of Management Studio and simply saved them.

You will go from an assured time out to a few seconds of waiting.

Rv3
  • 59
  • 6
0

In MSSMS select the table in the object explorer. Right click and select modify. That will bring a new tab where you can drag the columns into a new default order. Save and presto! Done.

Doug
  • 1
0

Steps:

  1. Rename the original table to tablename_temp
  2. create a new table containing the new column
  3. insert into tablename select * from tablename_temp
  4. recreate foreign keys and other constraint on the new table
Martin Staufcik
  • 8,295
  • 4
  • 44
  • 63
0

Short answer: It's not possible.

But you may try these steps:

  1. Right click table name on object explorer
  2. Click tasks
  3. Click drop and create table
  4. Add your columns in the position you want to add them

If you have data. Copy the data and paste it on an Excel spreadsheet, edit the spreadsheet to include new columns,edit top 100 rows and paste the data back into the table.

Goodluck

Onga Leo-Yoda Vellem
  • 296
  • 3
  • 10
0

According to Change Column Order in a Table, this operation is not supported using the Transact-SQL statement.

KyleMit
  • 30,350
  • 66
  • 462
  • 664
ryan huh
  • 61
  • 1
  • 2
-2

You have to create another table and copy the data. But have a look at "ordinal position" and try to update it ?

SELECT 
   ORDINAL_POSITION
  ,COLUMN_NAME
  ,DATA_TYPE
  ,CHARACTER_MAXIMUM_LENGTH
  ,IS_NULLABLE
  ,COLUMN_DEFAULT
FROM   
  INFORMATION_SCHEMA.COLUMNS 
WHERE   
  TABLE_NAME = 'Product' 
ORDER BY 
  ORDINAL_POSITION ASC;

Primary key is another question for which you may find lots of answers.

Louis
  • 2,854
  • 2
  • 19
  • 24