Repositioning a large table column

I need to reposition a column on a large table
but everytime the EM will time out. Am I not
waiting long enough and if so how long do I have
to wait. Is there a better way to go about this ?Don't do it. You can query the columns in the order you want them.
In EM when you do this, it is actually dropping and recreating the table.
You can imagine how much work this is for a large table.
If you beleive that you have a good reason to do this, post it and maybe
someone can convince you of the errors of your ways. :-)
"Phil396" <anonymous@.discussions.microsoft.com> wrote in message
news:5f8b01c524be$f7f60990$a601280a@.phx.gbl...
>I need to reposition a column on a large table
> but everytime the EM will time out. Am I not
> waiting long enough and if so how long do I have
> to wait. Is there a better way to go about this ?|||Why does it matter what "position" the column is in?
You can select the data any way you want:
SELECT ColA, ColB
FROM Tbl
or
SELECT ColB, ColA
FROM Tbl
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Phil396" <anonymous@.discussions.microsoft.com> wrote in message
news:5f8b01c524be$f7f60990$a601280a@.phx.gbl...
> I need to reposition a column on a large table
> but everytime the EM will time out. Am I not
> waiting long enough and if so how long do I have
> to wait. Is there a better way to go about this ?|||EM recreate the table (create a new table, move data, constraints, etc. and
then rename the table) in order to do this. Doing this in a large table will
consume time.
Why do you need to do this?
AMB
"Phil396" wrote:

> I need to reposition a column on a large table
> but everytime the EM will time out. Am I not
> waiting long enough and if so how long do I have
> to wait. Is there a better way to go about this ?
>|||Create a new table populate it with the old data and then drop the old
table. That is what EM is doing behind the scenes.
Why do you want to reposition a column? Sometimes that's handy in
development but on a production system it shouldn't matter. Column
order is determined by the order the columns are listed in a SELECT
statement, not by the physical order in the table. Don't use SELECT *
in production code and you won;t have to worry about column order.
David Portas
SQL Server MVP
--|||I could give a ___ ____ ____ what position the columns
are in but my boss ( this is the guy who signs my
paycheck ) would like it positioned in the database in a
certain order. Please if you have a sesame street answer
for this problem please keep it to yourself.

>--Original Message--
>Why does it matter what "position" the column is in?
>You can select the data any way you want:
>SELECT ColA, ColB
>FROM Tbl
>or
>SELECT ColB, ColA
>FROM Tbl
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Phil396" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5f8b01c524be$f7f60990$a601280a@.phx.gbl...
>
>.
>|||"Phil396" <anonymous@.discussions.microsoft.com> wrote in message
news:5ff501c524c7$709c4af0$a601280a@.phx.gbl...
> I could give a ___ ____ ____ what position the columns
> are in but my boss ( this is the guy who signs my
> paycheck ) would like it positioned in the database in a
> certain order. Please if you have a sesame street answer
> for this problem please keep it to yourself.
Maybe you can create a view for your boss to look at and then get back to
doing real work?
As other posters mentioned, the only way to do this is to drop the table,
create a new one, and insert all the rows -- that's what EM does, and that's
why it's incredibly slow.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Yes I was afraid something like this was going on.
We have hundreds of tables and many with too
many columns. That is not to metion the a billion rows
of data in those tables. In SQL Server 2005 this problem
should be address a better way because we have a lot of
contractors who will come in and get lost because cetain
columns will be located not were you expect them to be.
Thanks

>--Original Message--
>"Phil396" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5ff501c524c7$709c4af0$a601280a@.phx.gbl...
a
answer
>Maybe you can create a view for your boss to look at and
then get back to
>doing real work?
>As other posters mentioned, the only way to do this is
to drop the table,
>create a new one, and insert all the rows -- that's what
EM does, and that's
>why it's incredibly slow.
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>.
>|||you should really try to reason with your boss - or implement Adam's clever
solution.. but if neither works - and i really don't think it's something
you should do, but nevertheless.. you can update colid column in syscolumns
directly. first, set 'allow updates' option on:
exec sp_configure 'allow updates', 1
reconfigure with override
and then you can:
update syscolumns set colid=xx where id=object_id('table_name') and
name='column_name'
there is a unique constraint on id and colid, so you will have to be
creative when swapping colids :)
make sure you turn 'allow updates' off when done. and really, really, try
other solutions before doing this.
dean
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23vco%23gMJFHA.2356@.TK2MSFTNGP14.phx.gbl...
> "Phil396" <anonymous@.discussions.microsoft.com> wrote in message
> news:5ff501c524c7$709c4af0$a601280a@.phx.gbl...
> Maybe you can create a view for your boss to look at and then get back to
> doing real work?
> As other posters mentioned, the only way to do this is to drop the table,
> create a new one, and insert all the rows -- that's what EM does, and
that's
> why it's incredibly slow.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||From BOL:
System tables should not be altered directly by any user. For example, do
not attempt to modify system tables with DELETE, UPDATE, or INSERT
statements, or user-defined triggers.
"Dean" <dvitner@.nospam.gmail.com> wrote in message
news:uUi69aNJFHA.580@.TK2MSFTNGP15.phx.gbl...
> you should really try to reason with your boss - or implement Adam's
> clever
> solution.. but if neither works - and i really don't think it's something
> you should do, but nevertheless.. you can update colid column in
> syscolumns
> directly. first, set 'allow updates' option on:
> exec sp_configure 'allow updates', 1
> reconfigure with override
> and then you can:
> update syscolumns set colid=xx where id=object_id('table_name') and
> name='column_name'
> there is a unique constraint on id and colid, so you will have to be
> creative when swapping colids :)
> make sure you turn 'allow updates' off when done. and really, really, try
> other solutions before doing this.
> dean
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%23vco%23gMJFHA.2356@.TK2MSFTNGP14.phx.gbl...
> that's
>