Alter table statement

When you issue an alter table statement to add columns it will add them at
the end of the table. Is it possible to specify a column id and tell it
where you want the column added?
Say you have a table with 10 columns and you want to alter the table and add
a column into the middle of the table. Can you alter the table and specify
colid 5?
Any help is appreciated.Andy wrote:
> When you issue an alter table statement to add columns it will add them at
> the end of the table. Is it possible to specify a column id and tell it
> where you want the column added?
> Say you have a table with 10 columns and you want to alter the table and a
dd
> a column into the middle of the table. Can you alter the table and specif
y
> colid 5?
> Any help is appreciated.
No. You have to drop the table and recreate it. Column order matters
but if you have good programming practices (avoid SELECT * for example)
then it will not matter too much.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi
I do this sort of stuff in enterprise manger, I believe that behind the
sceans this creates your new table and then insert rows from the old table s
o
I suppose that the DDL has to be hand crafted if you choose to use the alter
table
"Andy" wrote:

> When you issue an alter table statement to add columns it will add them at
> the end of the table. Is it possible to specify a column id and tell it
> where you want the column added?
> Say you have a table with 10 columns and you want to alter the table and a
dd
> a column into the middle of the table. Can you alter the table and specif
y
> colid 5?
> Any help is appreciated.|||Hi Andy
There is no such thing as the 'middle of the table'. On the actual physical
pages where your data rows are stored, the order of columns might have no
relationship to the order in which the columns were created, or the order
they are returned when you SELECT *
If you want the columns returned in a particular order, list them in that
order. It is never a good idea to use SELECT *, for many reasons.
If you really really really want a column to be returned in a particular
position when you SELECT *, you'll have to re-create the table. That is what
the Enterprise Manager does behind the scenes and it can be a LOT of work
for a big table.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:2AEA34D6-E743-4E16-A3C0-9DDA2F12A8B5@.microsoft.com...
> When you issue an alter table statement to add columns it will add them at
> the end of the table. Is it possible to specify a column id and tell it
> where you want the column added?
> Say you have a table with 10 columns and you want to alter the table and
> add
> a column into the middle of the table. Can you alter the table and
> specify
> colid 5?
> Any help is appreciated.|||Kalen Delaney (replies@.public_newsgroups.com) writes:
> There is no such thing as the 'middle of the table'. On the actual
> physical pages where your data rows are stored, the order of columns
> might have no relationship to the order in which the columns were
> created, or the order they are returned when you SELECT *
I guess that Andy means "in the middle of the table definition". And
at least my table definitions have a middle, and then they have a top
and a bottom or a left and a right, depending on how I look at them.

> If you want the columns returned in a particular order, list them in that
> order. It is never a good idea to use SELECT *, for many reasons.
SELECT * does not belong in application code, but I gladly admit that
I type SELECT * many times a day for ad hoc queries for testing, debugging
and supporting issues. For me it's very important to have colunms in
some sort of logical order.
There is also the important relation to the database documentation. Our
system have a long history, and reading table descriptions where columns
appear in historical order would make matters even worse for new kids
in our block.
Most database design tools permits to add new columns anywhere in
the table (sic!), so it's a shortcoming of SQL of not supplying that
syntax, but forcing people to the risky path creating a new table
and move over data. (It's risky, because there are a couple of errors
you can make. Just look at EM - it has a couple of bugs with its
scripts.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx