14

I am using SQL Server 2008 R2 and I wish to add a new column into a specific spot in the column order in a table and not at the end. I wish to avoid having to recreate the table. How do I do this without recreating the table?

i.e.

Table: Bob
==========
   Col1
   Col2

Add new Column.

Table: Bob
==========
   Col1
   NewCol
   Col2
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
GordyII
  • 7,067
  • 16
  • 51
  • 69
  • 2
    Why would you have to recreate the table, and why does the column order matter to you? – John Saunders Feb 15 '11 at 02:50
  • Possible duplicates: [Add a new table column to specific ordinal position](http://stackoverflow.com/questions/769828/add-a-new-table-column-to-specific-ordinal-position), [Add Column on SQL Server on Specific Place?](http://stackoverflow.com/questions/3968954/add-column-on-sql-server-on-specific-place) – Devendra D. Chavan Feb 15 '11 at 02:57
  • Thanks for the links, however they all mention recreating the table. – GordyII Feb 15 '11 at 03:12
  • partitions / partition switching require the exact same definition between tables, including column order. – Jeff Maass Mar 12 '13 at 18:18

4 Answers4

20

You cannot. Column are always added at the end of the column list. The only way to change the order is to recreate the table from scratch.

That being said, it should never ever matter to you what is the actual physical order of the columns, nor the logical order of column definitions. If you have dependencies on column order, your code is broken. If you expect performance gains from column order, those are myths.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • 2
    Agreed. However, often it is nice (esp with a large table) to have common fields like your primary key as your first column. Other than human readability, there is ZERO importance to column order, BUT - if you insist, one thing you could do would be to create a new table, bulk export from the old table, bulk insert into the new table, and then do a drop/sp_rename. You'll need to be sure you prevent any changes happening to the original table during this time, or run a sync-up insert/update script before the drop/rename within the same transaction. – mattmc3 Feb 15 '11 at 03:18
  • 3
    Yes, the only reason to do this is human readability, and only then if there was an easy way to do it. It appears that there is no easy without recreating the table. Thank you all for answering the question. – GordyII Feb 15 '11 at 22:30
9

Don't do it. This goes together with the advise to never use SELECT *, in which case since you are listing all columns why does it matter their internal order?

That said, if you absolutely must, here's a demo that doesn't drop the table. The columns have to be dropped though, since you can't insert in the middle.

create table BOB(col1 int, colspace int, col2 int, col3 varchar(10))
insert BOB values (1,3, 2,'test')
;
alter table BOB add col2_copy int, col3_copy varchar(10), NewCol datetime
;
update BOB set col2_copy = col2, col3_copy = col3
;
alter table BOB drop column col2
alter table BOB drop column col3
;
alter table BOB add col2 int, col3 varchar(10)
;
update BOB set col2 = col2_copy, col3 = col3_copy
;
alter table BOB drop column col2_copy
alter table BOB drop column col3_copy
;

select * from BOB

It becomes significantly more difficult once you have constraints and defaults involved.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • 1
    Sorry to rain on your parade, but after you do all this run `select * from sys.system_internals_partition_columns ic join sys.partitions p on ic.partition_id = p.partition_id where object_id = object_id('BOB')` and look careful at the result. And if you still have doubts, look at the leaf_offset of column_id 8. `DROP COLUMN` doesn't drop anything, simply marks the column as 'unused'. Specially fixed columns, they will still occupy all that space. – Remus Rusanu Feb 15 '11 at 05:57
  • 2
    Now, if you throw in an ALTER TABLE... REBUILD at the end then yes, the columns are dropped. But you still did recreate the table (albeit internally, and keeping all constraints and permissions in place). Mostly the difference is academic, but when the size of data is prohibitive then the fact that the table was rebuild, one way or another, does make all the difference (ie. it cannot be done). – Remus Rusanu Feb 15 '11 at 06:02
  • @remus The answer was academic anyway, since the first few word are **not** to do it. It serves to show how to move the columns (visually) for whatever front end could want such a specific ordering (internally) `maybe it is a DAL that only uses select *`. Please feel free to add an answer to the question with specifics and deeper detail. – RichardTheKiwi Feb 15 '11 at 06:52
1

There's no way I've seen to script what you are trying to do. However in SSMS (in v 10.5 at least) you can right click on a table in the object explorer, and choose Design. This permits you to insert a column anywhere in the column order. It also preserves things on your table such as FK references, etc.

I haven't researched to verify what SQL Server is actually doing behind the scenes and I don't use this for rollouts myself but it is there.

Anon Coward
  • 11
  • 1
  • 1
    Depending on the specific definition of the new column definition, this might still recreate the table. – SchmitzIT Oct 26 '12 at 08:34
-2

you can do this by right click the table and go to design and change the order by drag and drop.

user4270993
  • 1