How to Add New Column To Table At A Particular Position?

Siv

Hi,
I am looking to add a new column to an existing table using this statement:

ALTER TABLE Seller ADD AQRBestPractice12M int NULL ;

What I would like to do is add the field AQRBestPractice12M next to some
existing fields but looking at the ALTER TABLE command can't see any way to
set the position within the table?

Any help appreciated.

Siv

Kalen Delaney

Hi Siv

There is no way to do this with ALTER TABLE. The Graphical tools allow this,
but behind the scenes they are actually creating a new table with the column
in the desired order, moving all the data to the new table, creating all the
necessary indexes, constraints, triggers, etc, dropping the original table,
and renaming the new table to the original name. For a table with millions
of rows, this can be a LOT of work. If you want a column in a particular
order, you'll have to do the same thing with TSQL statements, or use the
graphical interface.

Or, you can reconsider whether it is really important to have the column in
that position. There is no performance reason for putting a column in any
particular position, and since you should always be selecting data by name
the columns you want, you can always retrieve the data in the appropriate
order.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com


"Siv" <g@removethistextsivill.com> wrote in message
news:eTLoUuxHIHA.4592@TK2MSFTNGP02.phx.gbl...
> Hi,
> I am looking to add a new column to an existing table using this
> statement:
>
> ALTER TABLE Seller ADD AQRBestPractice12M int NULL ;
>
> What I would like to do is add the field AQRBestPractice12M next to some
> existing fields but looking at the ALTER TABLE command can't see any way
> to set the position within the table?
>
> Any help appreciated.
>
> Siv
>

David Portas

"Siv" <g@removethistextsivill.com> wrote in message
news:eTLoUuxHIHA.4592@TK2MSFTNGP02.phx.gbl...
> Hi,
> I am looking to add a new column to an existing table using this
> statement:
>
> ALTER TABLE Seller ADD AQRBestPractice12M int NULL ;
>
> What I would like to do is add the field AQRBestPractice12M next to some
> existing fields but looking at the ALTER TABLE command can't see any way
> to set the position within the table?
>
> Any help appreciated.
>
> Siv
>

You can't. You would have to recreate the table and repopulate it with data.

Column order is generally determined by the order of columns in a SELECT
statement, not the order in which they are created. For that reason you may
find that the cost of recreating the table is greater than any conceivable
benefit.

--
David Portas

Geoff Schaller

Kalen and David,

> that position. There is no performance reason for putting a column in any
> particular position, and since you should always be selecting data by name

This is correct of course but there is a reason it can be useful to move
columns around: for code developers and repairers. When you have to
constantly open a table in a tool or designer, nothing ever fits on the
open page so you find yourself scrolling across or down to find relevant
fields. Build a table adapter or two with some complex relations in
Visual Studio and you will see what I mean :-).

I stress that this is cosmetic and of benefit only to the code designer
but it can be a productivity enhancement during the development phase.
We also have some business rules of our own when building databases that
work to code based solutions: key columns first, important data fields
next, auxiliary fields last. We also group like data types together. For
example, if a table has 120 columns and you are looking for a datetime
value, it is a lot faster to detect the datetime block and then find the
column of interest.

So quite often, if I take over someone's system and find myself
constantly in specific tables, I will often move the columns around for
my benefit. It is only a small imposition on the client once.

Yes it is cosmetic but it is surprising how much easier it makes the
coding.

Geoff Schaller
Software Objectives

Erland Sommarskog

Siv (g@removethistextsivill.com) writes:
> I am looking to add a new column to an existing table using this
> statement:
>
> ALTER TABLE Seller ADD AQRBestPractice12M int NULL ;
>
> What I would like to do is add the field AQRBestPractice12M next to some
> existing fields but looking at the ALTER TABLE command can't see any way
> to set the position within the table?

There is no direct support for this, and asking for this is likely to whip
comments from purists who says "why do you want to do that"?

You do not have to tell *me* why. I always place my new columns carefully
when design new features our database.

But the way to do is to 1) Rename the existing table. 2) Create the new
definition. 3) Move data over. 4) Move referencing foreign keys. 5) Drop
the old definition. 6) Restore triggers, foreign keys and indexes. And
on top of this you need some error/transaction handling, so that you
don't lose something.

It's certainly nothing you do easily, unless you have set up a routine
for it. Which, is worth noting, is worth having, since not all schema
changes can be carried out with ALTER TABLE anyway. But if you are
uncertain of the procedure, it is difficult to recommend.

By the way, the tools in Enterprise Manager and Mgmt Studio may seem to
permit this, but they run the long way I described above. And they don't
do it right, so stay away from that.

Finally, there is an old item for this on Connect that you can vote on
if you wish:
https://connect.microsoft.com/SQLSer...dbackID=124781

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Siv

Guys,
Many thanks for your comments. I am glad that there is no easy way to do
this as I have looked high and low to find a way to do it and came up blank.

At the end of the day my reasons for wanting to do it are more cosmetic than
anything else, so given your comments I'll just accept the new field going
on the end. I just thought there may be some easy way to set the position
of each field that I just couldn't find.

Thanks again for your time answering my question, much appreciated.

Siv


"Siv" <g@removethistextsivill.com> wrote in message
news:eTLoUuxHIHA.4592@TK2MSFTNGP02.phx.gbl...
> Hi,
> I am looking to add a new column to an existing table using this
> statement:
>
> ALTER TABLE Seller ADD AQRBestPractice12M int NULL ;
>
> What I would like to do is add the field AQRBestPractice12M next to some
> existing fields but looking at the ALTER TABLE command can't see any way
> to set the position within the table?
>
> Any help appreciated.
>
> Siv
>

Kalen Delaney

I just said there is no PERFORMANCE reason. I readily admit you may have
reasons of your own. And if you are still in development, there may be few
enough rows in the table that it doesn't take hours to rebuild the table
with the new column.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com


"Geoff Schaller" <geoffx@softwareobjectivesx.com.au> wrote in message
news:472e4d28$1@dnews.tpgi.com.au...
> Kalen and David,
>
>> that position. There is no performance reason for putting a column in any
>> particular position, and since you should always be selecting data by
>> name
>
> This is correct of course but there is a reason it can be useful to move
> columns around: for code developers and repairers. When you have to
> constantly open a table in a tool or designer, nothing ever fits on the
> open page so you find yourself scrolling across or down to find relevant
> fields. Build a table adapter or two with some complex relations in Visual
> Studio and you will see what I mean :-).
>
> I stress that this is cosmetic and of benefit only to the code designer
> but it can be a productivity enhancement during the development phase. We
> also have some business rules of our own when building databases that work
> to code based solutions: key columns first, important data fields next,
> auxiliary fields last. We also group like data types together. For
> example, if a table has 120 columns and you are looking for a datetime
> value, it is a lot faster to detect the datetime block and then find the
> column of interest.
>
> So quite often, if I take over someone's system and find myself constantly
> in specific tables, I will often move the columns around for my benefit.
> It is only a small imposition on the client once.
>
> Yes it is cosmetic but it is surprising how much easier it makes the
> coding.
>
> Geoff Schaller
> Software Objectives
>

Liz

"Geoff Schaller" <geoffx@softwareobjectivesx.com.au> wrote in message
news:472e4d28$1@dnews.tpgi.com.au...

> This is correct of course but there is a reason it can be useful to move
> columns around: for code developers and repairers. When you have to
> constantly open a table in a tool or designer, nothing ever fits on the
> open page so you find yourself scrolling across or down to find relevant
> fields. Build a table adapter or two with some complex relations in Visual
> Studio and you will see what I mean :-).

you could always buy or build a tool that displays columns in whatever order
suits your shop protocols or style without actually physically rearranging
columns, no? or query the system tables and output what you need in
whatever order you want on an ad-hoc basis if you don't want to invest much
time or money

but I certainly know what you mean about "disorganized" tables ... when I
see them I sometimes just use DDL instead of the GUI to do whatever needs
doing

> I stress that this is cosmetic and of benefit only to the code designer
> but it can be a productivity enhancement during the development phase. We
> also have some business rules of our own when building databases that work
> to code based solutions: key columns first, important data fields next,
> auxiliary fields last. We also group like data types together. For
> example, if a table has 120 columns and you are looking for a datetime
> value, it is a lot faster to detect the datetime block and then find the
> column of interest.

unless that column is already logically grouped otherwise for another
purpose

> So quite often, if I take over someone's system and find myself constantly
> in specific tables, I will often move the columns around for my benefit.
> It is only a small imposition on the client once.

unless they have other people who will be working on the system who prefer a
different arrangement ... that's the rationale for a tool which virtualizes
the column display order to whatever you need

Geoff Schaller

Liz,

This wasn't meant to be a 'holier than thou' message. All I was doing
was presenting a reason why it can sometimes be advantageous to move
columns. If it is too difficult or too expensive then I wouldn't bother
either.

> you could always buy or build a tool that displays columns in whatever order
> suits your shop protocols or style without actually physically rearranging

:-) no. This implies I want to spend money to do something Management
Studio can do in a few seconds.

> but I certainly know what you mean about "disorganized" tables ... when I
> see them I sometimes just use DDL instead of the GUI to do whatever needs

I guess this is my main point. Sometimes it helps. Sometimes it doesn't.

> unless that column is already logically grouped otherwise for another
> purpose

In which case the developer will have left comments in his code
accordingly and no-one should come along a re-arrange for the sake of
it. Usually it is clear looking at the code and table structure when
this applies.

Cheers,

Geoff

Hugo Kornelis

On Mon, 5 Nov 2007 03:09:06 +0000, Geoff Schaller wrote:

>> you could always buy or build a tool that displays columns in whatever order
>> suits your shop protocols or style without actually physically rearranging
>
>:-) no. This implies I want to spend money to do something Management
>Studio can do in a few seconds.

Hi Geoff,

Management Studio can only do this in a few seconds if your tables hold
relatively few rows. The actions carried out behind the scenes by SSMS
involve creating a new table and copying all content - imagine how much
time that will cost on a 500 GB table!

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis