5

I'm altering an existing table to add an Identity column. That I can do, no problem.

But I'm wanting to be sure that people who look at it in the future will see that it has the identity column added, so I really want to make it column 1. I know this is totally inconsequential to the system's operation; it's strictly for human reading.

Does anyone know of a way to do this? I've looked at the TSQL syntax for Alter Table and for column_definition, and don't see anything; but I'm hoping someone knows of a way to make this happen.

FWIW, this is a one-time operation (but on many servers, so it needs to be automated), so I'm not worried whether any "trick" might go away in the future -- as long as it works now. We're using recent versions of SQL Server Express.

Thanks for any suggestions.

Joe Baker
  • 186
  • 1
  • 1
  • 11
  • 1
    you can rename the current table, create a new table with same schema and identity column as first column and copy the data. – radar Oct 16 '14 at 18:12
  • 1
    I only know how in SSMS. And be aware it really has move data around if it is not the last column. So if there are big table is can take a while. Would a View work? – paparazzo Oct 16 '14 at 18:14
  • I'm with @Blam, I'd just create a view that orders the columns the way you want them to be. – alroc Oct 16 '14 at 18:18
  • AHiggins, sorry -- I looked for this to have been asked earlier, and must have used the wrong keywords. Yes, this is a repeat of that question. – Joe Baker Oct 16 '14 at 18:21
  • Alternatively, read Paul's answer [here](http://stackoverflow.com/questions/12671629/how-to-add-a-column-at-a-specific-position-in-a-table) – AHiggins Oct 16 '14 at 18:28
  • OK I will ask about the white elephant in the room here. Why do you care which position a given column is in a table. The order of columns really doesn't make any difference unless you are doing something using ordinal position (which is a sign you should stop doing that). You should always reference columns by name. When you use the name the position in the table makes no difference. – Sean Lange Oct 16 '14 at 18:39
  • Quoting from the initial question: "I know this is totally inconsequential to the system's operation; it's strictly for human reading." – Joe Baker Oct 20 '14 at 15:20

3 Answers3

2

Solve this by following these steps:

-- First, add identity column
alter table
    mytable

add
    id int identity(1, 1) not null

-- Second, create new table from existing one with correct column order
select
    id,
    col1,
    col2

into
    newtable

from
    mytable

Now you've got newtable with reordered columns. If you need to you can drop your mytable and rename newtable to mytable:

drop table
    mytable

exec sp_rename
    'newtable', 'mytable'
Beetee
  • 475
  • 1
  • 7
  • 18
timo.rieber
  • 3,727
  • 3
  • 32
  • 47
1

It is not possible with ALTER statement. If you wish to have the columns in a specific order, you will have to create a newtable, use INSERT INTO newtable (col-x,col-a,col-b)SELECT col-x,col-a,col-b FROM oldtable to transfer the data from the oldtable to the newtable, delete the oldtable and rename the newtable to the oldtable name.

This is not necessarily recommended because it does not matter which order the columns are in the database table. When you use a SELECT statement, you can name the columns and have them returned to you in the order that you desire.

USING OBJECT EXPLORE

Avoid this step.. because ssms tools gives you to do light Data administration, while going for changes with multiple column record ,you may end with loosing some data..etc..because how fast your processor is it will always hang for changing architecture..

And once data lost..you will be no where to fetch them back...happened with me oncw..

Shekhar Pankaj
  • 9,065
  • 3
  • 28
  • 46
  • Yeah, I've already got that solution coded, but thought I'd see if anyone here knew of a trick of some sort. Looks like no soap. I'll have to decide between having the identity at the end, where it may confuse some future programmer, or taking the small risk associated with the rename/new table/copy data solution. Thanks. – Joe Baker Oct 16 '14 at 18:20
  • New update to answer, you will like it too..some more don,t added – Shekhar Pankaj Oct 16 '14 at 18:45
0

According to Microsoft you can do this only using SQL Server Management Studio. Check this

geoandri
  • 2,360
  • 2
  • 15
  • 28
  • Aha. I suspected that's the case; I even have the rename/create new/copy data/delete table solution coded up; I was just hoping to avoid it if possible. Thanks. – Joe Baker Oct 16 '14 at 18:18
  • You can duplicate the same functionality Microsoft uses with a SQL script ... in fact, the interface itself includes an option to generate that script. It isn't limited to the Management Studio graphical interface. – AHiggins Oct 16 '14 at 18:21
  • Yup, when i said I have it "coded up", it's about 90% from that generated script. The thing is, this may execute 5000 miles away, on a non-networked computer, at 2:00 in the morning my time -- and if it fails for some crazy reason, I'm screwed. So we'll probably just live with it in the "wrong" position. Thanks, though. – Joe Baker Oct 16 '14 at 18:25