Re-ordering Table Fields

When I use the ALTER TABLE statement to Add new fields to a table, is there a
way to specify it's position on the table? I don't want it to be in the end
(last position) of the fields...
In Enterprise Manager, it does this by making a copy of the table with new
structure, droping the old and renaming the new... This is no god when I add
fields to a table with 1 million regs, in production environment... But I
also want to keep the structure the same as the development environment...
Does anyone knows how can I do that simply with T-SQL commands?
Thanks!
http://vyaskn.tripod.com/administration_faq.htm#q11
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Rafa" <Rafa@.discussions.microsoft.com> wrote in message
news:3BC02B7C-2B8A-4A3A-9D3C-ABC7134A9F8D@.microsoft.com...
When I use the ALTER TABLE statement to Add new fields to a table, is there
a
way to specify it's position on the table? I don't want it to be in the end
(last position) of the fields...
In Enterprise Manager, it does this by making a copy of the table with new
structure, droping the old and renaming the new... This is no god when I add
fields to a table with 1 million regs, in production environment... But I
also want to keep the structure the same as the development environment...
Does anyone knows how can I do that simply with T-SQL commands?
Thanks!
|||"Rafa" <Rafa@.discussions.microsoft.com> wrote in message
news:3BC02B7C-2B8A-4A3A-9D3C-ABC7134A9F8D@.microsoft.com...
> When I use the ALTER TABLE statement to Add new fields to a table, is
> there a
> way to specify it's position on the table? I don't want it to be in the
> end
> (last position) of the fields...
> In Enterprise Manager, it does this by making a copy of the table with new
> structure, droping the old and renaming the new... This is no god when I
> add
> fields to a table with 1 million regs, in production environment... But I
> also want to keep the structure the same as the development environment...
> Does anyone knows how can I do that simply with T-SQL commands?
> Thanks!
Is there any particular reason why the column *needs* to be in a different
location? SQL Server internally will move the data around in it's storage
scheme depending on what datatype the column has.
You can use EM to do this, but it basically copies the data to a temp table,
drops the original table, recreates the original table and then copies the
data back in.
Rick Sawtell
MCT, MCSD, MCDBA