Column Order in Table

Hi,
I know that physical order of a column is not important in tables but I like
to know if it is possible to force column order when I alter a table to add
column or change the order later.
It seems that the orders are stored in syscolumns table but I think there
should be a system sp/func to alter that.
Thanks,
Leila> I know that physical order of a column is not important in tables but I
like
> to know if it is possible to force column order when I alter a table to
add
> column or change the order later.
Only by dropping the table and re-creating it.

> It seems that the orders are stored in syscolumns table but I think there
> should be a system sp/func to alter that.
No, do not attempt this. Why do you care where the column is?
http://www.aspfaq.com/
(Reverse address to reply.)|||In addition to what Aaron said...
From BOL
System tables should not be altered directly by any user.
Don't do this, ever.
I tried this as an experiment in a test database once and screwed up royally
the database.
Good thing it was a test database as I had no other choice then to delete
it.
There is no system sp/func for this and there probably never will be as the
order of the columns is not important. You can reorder columns in Enterprise
Manager but behind the scenes it drops and recreates the table.
"Leila" <Leilas@.hotpop.com> wrote in message
news:eZRp6Ox0EHA.3616@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I know that physical order of a column is not important in tables but I
like
> to know if it is possible to force column order when I alter a table to
add
> column or change the order later.
> It seems that the orders are stored in syscolumns table but I think there
> should be a system sp/func to alter that.
> Thanks,
> Leila
>|||> No, do not attempt this. Why do you care where the column is?
Just interested!
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uKMreex0EHA.3408@.tk2msftngp13.phx.gbl...
> like
> add
> Only by dropping the table and re-creating it.
>
there[vbcol=seagreen]
> No, do not attempt this. Why do you care where the column is?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||FWIW, I was interested too, just on general principle. I know it doesn't
"matter" all that much, but I want to know how to do whatever I want with
the data.
I knew it was possible to do it in the Enterprise Manager, but I didn't know
it was dropping and recreating the table. Thanks.
"raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
news:OFpVmvx0EHA.3500@.TK2MSFTNGP09.phx.gbl...
> In addition to what Aaron said...
> From BOL
> System tables should not be altered directly by any user.
> Don't do this, ever.
> I tried this as an experiment in a test database once and screwed up
> royally
> the database.
> Good thing it was a test database as I had no other choice then to delete
> it.
> There is no system sp/func for this and there probably never will be as
> the
> order of the columns is not important. You can reorder columns in
> Enterprise
> Manager but behind the scenes it drops and recreates the table.
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:eZRp6Ox0EHA.3616@.TK2MSFTNGP11.phx.gbl...
> like
> add
>|||If you want to see the code:
In Enterprise manager open a table in design mode
Change the order of a column (don't save the change)
Click the "Save Change Script" icon
In one of my base tables, this produced 287 lines of code.
"Paul Pedersen" <no-reply@.swen.com> wrote in message
news:u0A2Zx$0EHA.2884@.TK2MSFTNGP11.phx.gbl...
> FWIW, I was interested too, just on general principle. I know it doesn't
> "matter" all that much, but I want to know how to do whatever I want with
> the data.
> I knew it was possible to do it in the Enterprise Manager, but I didn't
know
> it was dropping and recreating the table. Thanks.
>
> "raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
> news:OFpVmvx0EHA.3500@.TK2MSFTNGP09.phx.gbl...
delete[vbcol=seagreen]
there[vbcol=seagreen]
>|||Even more useful info! Thanks again.
You might have noticed, I'm new to SQL Server (from FoxPro).
"raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
news:uQotB3$0EHA.1188@.tk2msftngp13.phx.gbl...
> If you want to see the code:
> In Enterprise manager open a table in design mode
> Change the order of a column (don't save the change)
> Click the "Save Change Script" icon
> In one of my base tables, this produced 287 lines of code.
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:u0A2Zx$0EHA.2884@.TK2MSFTNGP11.phx.gbl...
> know
> delete
> there
>|||Understand that you just want to know more about the clockworks of SS, under
the hood. That's laudable; however, because SS is a physical system, it is
limited to physical media and, thus, must store information about column or
der because, as a physical system, it must manipulate the information at the
physical level.
The downfall of most DBMS products is that they often expose certain physica
l characteristics that should have been shielded from end-users...even Datab
ase Administrators, Engineers, and Developers. This is just another case wh
ere this is not so.
Any DML should manipulate column-level information on a NAME basis only, and
, thus, ordinal position is irrelevant. Therefore, any attempt to alter thi
s is meaningless. Now, you can affect the outcome but, as the other respond
ents have said, you must drop and recreate or create a temp table, migrate t
he data, drop the original, and, then, rename the temp. This is how the Vis
ual Database designer does it.
Sincerely,
Anthony Thomas
--
"Leila" <Leilas@.hotpop.com> wrote in message news:eZRp6Ox0EHA.3616@.TK2MSFT
NGP11.phx.gbl...
Hi,
I know that physical order of a column is not important in tables but I li
ke
to know if it is possible to force column order when I alter a table to ad
d
column or change the order later.
It seems that the orders are stored in syscolumns table but I think there
should be a system sp/func to alter that.
Thanks,
Leila|||Anthony,
It's arguable that DBMSs would be better if they stayed closer to the relati
onal model, but since column ordinal positions are part of the ANSI SQL stan
dard, I think it's appropriate to provide that attribute of a column in meta
data. If it's a downfall of anything, it's a downfall of ANSI SQL, not each
DBMS. SQL Server doesn't expose physical column order to the user, and whi
le it doesn't, it could even vary from row to row without the user knowing (
it doesn't, but it could, so long as select * queries returned columns in or
der of their (virtual, and stored in metadata, not physical) ordinal positio
n.
SQL Server's column ordinal positions are not an exposed physical characteri
stic of the database. They are part of the metadata, just like column types
and names, and they aren't a reflection of the physical layout of the data.
Microsoft does document the way in which column data is stored within a ro
w, since it can be beneficial to know for troubleshooting, design, optimizat
ion, and so on. But no T-SQL language constructs exist to access the inform
ation that way, save perhaps for some undocumented DBCC commands. Fixed-len
gth columns are stored before variable-length columns, for example (regardle
ss of ordinal position) and without looking it up, I'm not sure whether the
ordinal position attribute of a column (which is exposed in the ANSI INFORMA
TION_SCHEMA metadata views) is even respected within those two categories.
Long data (text, ntext, image), can even be stored out of the row's data pag
e, and tables with a non-clustered index store some column data in more than
one place. The physical layout of data in a SQL Server table is not expose
d to the user.
That said, I agree that columns should almost always be named, and the few T
-SQL features that rely on the column's ordinal position should be avoided i
f at all possible.
Steve Kass
Drew University
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message news:OZec
UbL1EHA.1652@.TK2MSFTNGP11.phx.gbl...
Understand that you just want to know more about the clockworks of SS, under
the hood. That's laudable; however, because SS is a physical system, it is
limited to physical media and, thus, must store information about column or
der because, as a physical system, it must manipulate the information at the
physical level.
The downfall of most DBMS products is that they often expose certain physica
l characteristics that should have been shielded from end-users...even Datab
ase Administrators, Engineers, and Developers. This is just another case wh
ere this is not so.
Any DML should manipulate column-level information on a NAME basis only, and
, thus, ordinal position is irrelevant. Therefore, any attempt to alter thi
s is meaningless. Now, you can affect the outcome but, as the other respond
ents have said, you must drop and recreate or create a temp table, migrate t
he data, drop the original, and, then, rename the temp. This is how the Vis
ual Database designer does it.
Sincerely,
Anthony Thomas
--
"Leila" <Leilas@.hotpop.com> wrote in message news:eZRp6Ox0EHA.3616@.TK2MSFTNG
P11.phx.gbl...
Hi,
I know that physical order of a column is not important in tables but I like
to know if it is possible to force column order when I alter a table to add
column or change the order later.
It seems that the orders are stored in syscolumns table but I think there
should be a system sp/func to alter that.
Thanks,
Leila|||I do like to have some semblance of order in column order, if only for displ
ay and design purposes. For instance, it's a lot easier for me to deal with
a table if the columns name, address, city, state, and zip appear next to ea
ch other and in that order.
"Steve Kass" <skass@.drew.edu> wrote in message news:eSW%230rO1EHA.2192@.TK2MS
FTNGP14.phx.gbl...
Anthony,
It's arguable that DBMSs would be better if they stayed closer to the relati
onal model, but since column ordinal positions are part of the ANSI SQL stan
dard, I think it's appropriate to provide that attribute of a column in meta
data. If it's a downfall of anything, it's a downfall of ANSI SQL, not each
DBMS. SQL Server doesn't expose physical column order to the user, and whi
le it doesn't, it could even vary from row to row without the user knowing (
it doesn't, but it could, so long as select * queries returned columns in or
der of their (virtual, and stored in metadata, not physical) ordinal positio
n.
SQL Server's column ordinal positions are not an exposed physical characteri
stic of the database. They are part of the metadata, just like column types
and names, and they aren't a reflection of the physical layout of the data.
Microsoft does document the way in which column data is stored within a ro
w, since it can be beneficial to know for troubleshooting, design, optimizat
ion, and so on. But no T-SQL language constructs exist to access the inform
ation that way, save perhaps for some undocumented DBCC commands. Fixed-len
gth columns are stored before variable-length columns, for example (regardle
ss of ordinal position) and without looking it up, I'm not sure whether the
ordinal position attribute of a column (which is exposed in the ANSI INFORMA
TION_SCHEMA metadata views) is even respected within those two categories.
Long data (text, ntext, image), can even be stored out of the row's data pag
e, and tables with a non-clustered index store some column data in more than
one place. The physical layout of data in a SQL Server table is not expose
d to the user.
That said, I agree that columns should almost always be named, and the few T
-SQL features that rely on the column's ordinal position should be avoided i
f at all possible.
Steve Kass
Drew University
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message news:OZec
UbL1EHA.1652@.TK2MSFTNGP11.phx.gbl...
Understand that you just want to know more about the clockworks of SS, under
the hood. That's laudable; however, because SS is a physical system, it is
limited to physical media and, thus, must store information about column or
der because, as a physical system, it must manipulate the information at the
physical level.
The downfall of most DBMS products is that they often expose certain physica
l characteristics that should have been shielded from end-users...even Datab
ase Administrators, Engineers, and Developers. This is just another case wh
ere this is not so.
Any DML should manipulate column-level information on a NAME basis only, and
, thus, ordinal position is irrelevant. Therefore, any attempt to alter thi
s is meaningless. Now, you can affect the outcome but, as the other respond
ents have said, you must drop and recreate or create a temp table, migrate t
he data, drop the original, and, then, rename the temp. This is how the Vis
ual Database designer does it.
Sincerely,
Anthony Thomas
--
"Leila" <Leilas@.hotpop.com> wrote in message news:eZRp6Ox0EHA.3616@.TK2MSFTNG
P11.phx.gbl...
Hi,
I know that physical order of a column is not important in tables but I like
to know if it is possible to force column order when I alter a table to add
column or change the order later.
It seems that the orders are stored in syscolumns table but I think there
should be a system sp/func to alter that.
Thanks,
Leila