MS SQL Server - ALTER TABLE to add a column between other columns

Author

ALTER TABLE to add a column between other columns

Pasquale

2005-11-08, 9:23 am

I need to use ALTER TABLE in order to add a column between the columns of a
specific table. The table is in an production environment.
I think to use a unique Transact-SQL statement that allows to alter the
previous colums and add my column in the right position inside structure
table.
I have used this statement:
ALTER TABLE mytable
ALTER COLUMN mypreviouscolumn typecolumn(precision
, scale)
ADD COLUMN mycolumn typecolumn(precision
, scale)
but I have generated a syntax error.
How can I solve this issue?

Many thanks

Kalen Delaney

2005-11-08, 3:56 pm

This is not possible with ALTER TABLE.
It really shouldn't be necessary, anyway. The order that the columns are
returned when you SELECT * is not necessarily the order they are physically
stored on the data pages. If you want to return columns in a particular
order, you can SELECT with a column list, or create a view of the table with
the columns in the order you want them.

The graphical tools make you think you can add a column in a particular
position, but they do this by completely recreating a new table. That can
take a long time on a big table.

--
HTH
Kalen Delaney, SQL Server MVP
www. solidqualitylearning
.com

"Pasquale" < Pasquale@discussions
.microsoft.com> wrote in message
news:246FA243-3EAA-40C5-8AC7- 9462DD64B8AB@microso
ft.com...
>I need to use ALTER TABLE in order to add a column between the columns of a
> specific table. The table is in an production environment.
> I think to use a unique Transact-SQL statement that allows to alter the
> previous colums and add my column in the right position inside structure
> table.
> I have used this statement:
> ALTER TABLE mytable
> ALTER COLUMN mypreviouscolumn typecolumn(precision
, scale)
> ADD COLUMN mycolumn typecolumn(precision
, scale)
> but I have generated a syntax error.
> How can I solve this issue?
>
> Many thanks
>

Sponsored Links