placement of a new column

I was wondering if there is a way to add a new column to a table with
specific instruction as to where it should be created. i.e. after the 4th
column. Currently, a new column is added at the end of the table which I am
trying to avoid.
The solution I am looking for is via a script and not using the design table
option in the enterprise manager.
Many thanks
Shahriar> Currently, a new column is added at the end of the table which I am
> trying to avoid.
WHY'''''''? Can you please explain why
column order is important?
Enterprise Manager can kludge this for you, but PLEASE, PLEASE, PLEASE read
http://www.aspfaq.com/2528 before you try this on a table with anything more
than a handful of rows. You could certainly script something like what
Enterprise Manager does, but this is not using ALTER TABLE at all, and it is
a good 12kb worth of code. And as I already noted, you don't want to do
this on a large table, because it will make a complete copy of your existing
data before it's done. This can double (or more) your database size (albeit
temporarily). Plus the table is basically offline for that entire amount of
time, because you need to wrap the activity in a transaction...
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Nope. The only way to do that is to do it the way EM does. Build your new
table exactly how you want it, copy all data over to it, and then drop the
old table.
"Shahriar" <Shahriar@.discussions.microsoft.com> wrote in message
news:A0C8E551-5ED0-4C32-9929-904AA088FB82@.microsoft.com...
>I was wondering if there is a way to add a new column to a table with
> specific instruction as to where it should be created. i.e. after the
> 4th
> column. Currently, a new column is added at the end of the table which I
> am
> trying to avoid.
> The solution I am looking for is via a script and not using the design
> table
> option in the enterprise manager.
> Many thanks
> Shahriar|||From ELmars post:
"o Rename table with constraints and all (use sp_rename) Don't forget
referencing foreign keys.
o Create new table definition.
o INSERT data from the old table to the new table
o Create new referencing foreign keys pointing to new table.
o Drop old table
"
HTH, Jens Sü?meyer.
http://www.sqlserver2005.de
--|||"Jens S?meyer" > From ELmars post:
> "o Rename table with constraints and all (use sp_rename) Don't forget
> referencing foreign keys.
If I do it using EM, EM will take care of foreignkey, default, etc?|||Column order is not that important but if you need to really have it in a
specific
order, you'll need to dump the existing table into a temp table, re-create
the table with
the column ordering you want, and then re-insert the rows. To me, it
seems more trouble than it's worth.
"Shahriar" <Shahriar@.discussions.microsoft.com> wrote in message
news:A0C8E551-5ED0-4C32-9929-904AA088FB82@.microsoft.com...
> I was wondering if there is a way to add a new column to a table with
> specific instruction as to where it should be created. i.e. after the
4th
> column. Currently, a new column is added at the end of the table which I
am
> trying to avoid.
> The solution I am looking for is via a script and not using the design
table
> option in the enterprise manager.
> Many thanks
> Shahriar|||
"AB - MVP" wrote:

> WHY'''''''? Can you please explain w
hy
> column order is important?
>
There are few reasons that comes in my mind, consistency is one of them.
In dealing with many tables, in my case over 180 of them, the placement
order is indeed important. Here is a little example. All my tables have a
field at the end called dateCreated. Opening EM, I can quickly locate that
column.
Second, I ususaly like to place primary keys first, then index field key
nexts, and so on.. its a practice I have been accustomed to. Third, I
believe it is a practice that all DBA's have beed adapted to..All reserved
fields are usually placed at the end of the tables and not scattered around
in a table.
I hope that was a satisfactory answer to your question.
Thanks
Shahriar|||Yes it will.
"js" wrote:

> "Jens S¨1?meyer" > From ELmars post:
> If I do it using EM, EM will take care of foreignkey, default, etc?
>
>|||Shahriar,
Relational databases are not there to satisfy the whims and fancies of a
single user. The underlying framework is based on Completeness, Generality,
Formality & Simplicity, all with its foundation on set mathematics & logic.
It is this principle-based framework that makes relational databases
superior to alternatives.
Just because you like to do something with your table management interface
in the EM, does not make it a meaningful and valid reason for the DBMS to
have such a provision. Having look-a-like tables does not mean they are
"consistent" under the above mentioned framework.
Again, that is your personal choice. Some other user might prefer a
different choice.
There is no such general practice. Note that, there are certain specific
cases where SQL assigns positional significance to the order of columns in a
table, but that has nothing to do with user preferences or general
practices.
Anith|||Anith writes in regard to the relevancy of a column position in a table :
> Relational databases are not there to satisfy the whims and fancies of a
> single user. The underlying framework is based on Completeness ,
> Generality, Formality & Simplicity, all with its foundation on set
> mathematics & logic.
DO YOUR TABLES LOOK LIKE THIS'
Fname, Address4,Zip, Telephone, MI, DOB,Lname,Address1, State,
Address2,email,Address3
Shahriar
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OYMP3vkUFHA.928@.TK2MSFTNGP15.phx.gbl...
> Shahriar,
> Relational databases are not there to satisfy the whims and fancies of a
> single user. The underlying framework is based on Completeness,
> Generality, Formality & Simplicity, all with its foundation on set
> mathematics & logic. It is this principle-based framework that makes
> relational databases superior to alternatives.
>
> Just because you like to do something with your table management interface
> in the EM, does not make it a meaningful and valid reason for the DBMS to
> have such a provision. Having look-a-like tables does not mean they are
> "consistent" under the above mentioned framework.
>
> Again, that is your personal choice. Some other user might prefer a
> different choice.
>
> There is no such general practice. Note that, there are certain specific
> cases where SQL assigns positional significance to the order of columns in
> a table, but that has nothing to do with user preferences or general
> practices.
> --
> Anith
>