Adding a new column

Question

Adding a new column

Shabu George

SSC Enthusiast

Points: 121
More actions
August 10, 2004 at 3:52 am

#66822

Hai Friends,
I have a table with say 10 fiels, now i want to add a new column after the 5th field. how do i do this by T-SQL.
Thanks
Shabu

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

I don't think you can without dropping and re-creating the table. You can add the column to the end and then via EM move it or just create it in EM where you want it.

Please note that if you are running replication this may break it.

Good Hunting!

AJ Ahrens

webmaster@kritter.net

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 2

Pls ref BOL.

A. Alter a to add a new column

This example adds a column that allows null values and has no values provided through a DEFAULT definition. Each row will have a NULL in the new column.

CREATE TABLE doc_exa ( column_a INT) GOALTER TABLE doc_exa ADD column_b VARCHAR(20) NULLGOEXEC sp_help doc_exaGODROP TABLE doc_exaGO


 
thanks,
Ganesh

Bill Geake Ten Centuries Points: 1292 More actions · Answer 3

I think you should consider just why you need a column in a particular postion. If I remember it correctly, part of the relational database model is that column order is not significant since you can specify the returned column order in a query.

Unless you're doing a SELECT * (which none of us would ever do, right?).

Bill.

robertk SSCommitted Points: 1608 More actions · Answer 4

I'm in agreement. You should never need to actually have a specific column order on your tables. That's what your queries are for... it's easy enough to select the columns you need, in the order you want.

And yeah, select * = bad, especially on views and compiled stored procedures. Those make the SQL Server gods angry... er something.