Adding a Column in an Ordinal Position

I have a situation where I am using SQL Server 2005 and I need to be able to
add a column in a specific ordinal position. I have looked at BOL and I
wasn't able to find any mention of being able to specify Ordinal position.
Is there a way?
Thanks!
RonLogically there should be no reason to put a column in a particular slot but
you can do this via SSMS. Open the table in the table designer and insert a
column in between the two that you want. Just be aware that this will
create a new table, copy all the existing rows to it, drop the old one and
rename this one. Not something you usually care to do in a production env
but that is up to you. There is no way to do this via an alter table.
Andrew J. Kelly SQL MVP
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:u0TTgYYBGHA.636@.TK2MSFTNGP10.phx.gbl...
> I have a situation where I am using SQL Server 2005 and I need to be able
> to add a column in a specific ordinal position. I have looked at BOL and
> I wasn't able to find any mention of being able to specify Ordinal
> position.
> Is there a way?
> Thanks!
> Ron
>|||> I have a situation where I am using SQL Server 2005 and I need to be able
> to add a column in a specific ordinal position. I have looked at BOL and
> I wasn't able to find any mention of being able to specify Ordinal
> position.
> Is there a way?
ALTER TABLE does not support this syntax because there is really no reason
to do this. Your applications and queries do not care what order the
columns were specified / created, unless you are lazy and expect ordinal
position from "SELECT * " or "INSERT without column definition" to work.
Of course, there are kludge workarounds. But I'd be more interested in
hearing the justification for this...
Please read the following:
http://www.aspfaq.com/2528|||Without recreating the entire table with the column in appropriate position
in the CREATE TABLE statement, this is not possible.
There could be some specific exceptions however, in most cases, adding a
column in a specific position is almost meaningless since well written SQL
code generally relies on column names rather than column positions.
Anith|||> ... But I'd be more interested in
> hearing the justification for this...
>
Well, I sometimes insert a column into the middle of a table out of
consideration for the y who maintains the system - who may be me or
may be some other guy. IMO, it is nice to have attributes that are
logically connected (begin and end dates, for example) display together
in the schema. It just makes for easier reading.
My 2 cents.
Payson|||> may be some other guy. IMO, it is nice to have attributes that are
> logically connected (begin and end dates, for example) display together
> in the schema.
Why would you have a BeginDate column, and then later on decide you need an
EndDate column?
If it's important, drop and re-create the table. If you're expecting an
ALTER TABLE exception, don't hold your breath. "Logically connected" does
not have to mean "visually adjacent." Another way to simulate this is to
create a view with the columns in the order you'd like them to be, if
disrupting the existing table is out of the question.
A|||Are you saying that a table only makes sense if the columns are properly
adjacent to each other? Or are you saying that no documentation is needed if
the columns are "well aligned"?
IMHO understanding databases and tables begins with understandig the
business logic first, and not the other way around.
ML
http://milambda.blogspot.com/|||ML wrote:
> Are you saying that a table only makes sense if the columns are properly
> adjacent to each other? Or are you saying that no documentation is needed
if
> the columns are "well aligned"?
>
No. Instead, I am saying it is easier to read and understand the
schema if columns that belong together display together in the schema.
And I almost never trust the documentation for details. I have been
burned too many times by inaccurate or out of date documentation and
comments. When learning a system, I always look at the schema and the
code because that's what's running. Maybe I do that because I have
been an independent contractor for so long. I seem to always be
cleaning up someone else's mess. Oh, well, it's a living...
Payson|||Yeah. Cleaning up someone else's mess is a drag. That's why I write my own
documentation before I actually start making changes. Sometimes the schema
and the code don't reflect the actual business logic as much as they reflect
the mind-set of the author. Sadly.
ML
http://milambda.blogspot.com/