Add Column

Hallo,
is it possible to add a new column not at the end of a table,
e.g. with a "before [another_column]"-clause or something else ?
Best regardsYou can't specify column order via ALTER TABLE if that is what you are asking.

From a pure relational standpoint, the whole idea is wrong... In a relational world, the only place the columns or rows have any order is in result sets delivered to a client of some kind... Within the database, parts-is-parts and the order is meaningless. In other words, although you do care what order the columns appear, you really shouldn't.

My first suggestion would be to simply specify the order that you want the columns to appear in your SELECT statement. That way you can have things any way you'd like them.

My next suggestion would be to create a view that provides the columns in whatever order you like. This is easy to do, and relationally sound.

If for some reason you really truly want to specify the order of the columns within the table, the only way I know to do that is to:

1 make a backup, just in case!
2 create a work table with columns as you'd like them
3 copy the data from the old table to the work table
4 verify the copy
5 drop the old table
6 rename the work table using the old name

-PatP|||is it possible to add a new column not at the end of a table,
e.g. with a "before [another_column]"-clause or something else ?not in microsoft sql server, no (it is possible elsewhere)

listens to pat's advice, in the order he gave it --

1. specify column sequence in your SELECT
or
2. use a view
or
3. if you really must ...

i would go with 1.

:)|||Oh, Pat and Rudy aren't telling you the whole story because they are anti-gui, but you can do this easily through Enterprise Manager.

Edit the table through Enterprise Manager, ordering the columns however you like, and then either save the table directly or click the button to create a script for making the changes. You'll see from the script that SQL Server actually creates a new table under a different name, copies your data into it, drops the original table, and then renames the remaining table. It should also create script to handle any relational integrity with other tables.