Dear All
Can I Alter tables to change the columns orders, Or Add Column in a
specified order? How?
AhmedAhmed
I don't think that it make sense. It does not matter what is a column's
order though you can SELECT col1,col2 Or SELECT col,col1
If you persist, you can add the column by EM in order that you want.
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
news:%232KTvJ6sFHA.1568@.TK2MSFTNGP10.phx.gbl...
> Dear All
> Can I Alter tables to change the columns orders, Or Add Column in a
> specified order? How?
> Ahmed
>|||No and no. Only way is to re-create the table (which is what Enterprise Mana
ger does when you do it
graphically).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
news:%232KTvJ6sFHA.1568@.TK2MSFTNGP10.phx.gbl...
> Dear All
> Can I Alter tables to change the columns orders, Or Add Column in a specif
ied order? How?
> Ahmed
>|||> Can I Alter tables to change the columns orders, Or Add Column in a
> specified order? How?
More importantly, WHY?
http://www.aspfaq.com/2528|||The only reason I can think of for changing the order of columns is that you
want to use SELECT *. Using SELECT * is a common bad practice usually
committed by the lazy or the incompetent. I only use it in query analyzer
for debugging. SELECT * is one of the things I look for in profile traces
when I'm analyzing a system. If I find it in a trace, then I know that I
will have to spend a lot more time determining whether or not any change I
make will break existing code, and consequently, I will have to charge the
customer a lot more money at every stage of the project.
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
news:#2KTvJ6sFHA.1568@.TK2MSFTNGP10.phx.gbl...
> Dear All
> Can I Alter tables to change the columns orders, Or Add Column in a
> specified order? How?
> Ahmed
>|||Dear All
Thanks for replay
I know it's not a live or death feature, I need to arrange the columns
according to some rules, like if it is a primary key or doesn't allow null,
to make the table structure in client database same as the development
database, or anything else. I'm just asking how to do it.
Anyway, if we can do it through the enterprise manager I think it is not
difficult to make it through the T_SQL.
Thanks
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:Om6jiC7sFHA.524@.TK2MSFTNGP12.phx.gbl...
> The only reason I can think of for changing the order of columns is that
> you
> want to use SELECT *. Using SELECT * is a common bad practice usually
> committed by the lazy or the incompetent. I only use it in query analyzer
> for debugging. SELECT * is one of the things I look for in profile traces
> when I'm analyzing a system. If I find it in a trace, then I know that I
> will have to spend a lot more time determining whether or not any change I
> make will break existing code, and consequently, I will have to charge the
> customer a lot more money at every stage of the project.
> "Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
> news:#2KTvJ6sFHA.1568@.TK2MSFTNGP10.phx.gbl...
>|||Ahmed Hashish (a_hashish@.hotmail.com) writes:
> Thanks for replay
> I know it's not a live or death feature, I need to arrange the columns
> according to some rules, like if it is a primary key or doesn't allow
> null, to make the table structure in client database same as the
> development database, or anything else. I'm just asking how to do it.
> Anyway, if we can do it through the enterprise manager I think it is not
> difficult to make it through the T_SQL.
You are completely right, and Brian is wrong. There are several good reasons
why one want to have columns in a certain order, and SELECT * is not one
of them. But have PK columns first in the table, and in the correct order
is one. And wanting have logically related columns close to each other is
another.
Anyway, there is no syntax for this, not even in SQL 2005. What Enterprise
Manager does is to create a new table, and then move over data to that
table, and recreate foreign keys etc. In a script, I should add, that has
several serious flaws. While these are fairly easy to address, an ALTER
TABLE command would be easier to use.
On MSDN Product Feedback Centre you can submit bugs and suggestions for
SQL 2005, and then other people can vote on these submissions. One
suggestion that was submitted earlier this year, was precisely about this
matter. By now, it has assembled 21 votes, which I think makes it the
most voted-on suggestion for SQL 2005. So you are not the first one to
ask for this.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> You are completely right, and Brian is wrong. There are several good
reasons
> why one want to have columns in a certain order, and SELECT * is not one
> of them. But have PK columns first in the table, and in the correct order
> is one. And wanting have logically related columns close to each other is
> another.
The location of columns in a table is immaterial either from a performance
standpoint, or for any other reason. A primary key constraint always
creates an index, which means that the key values are copied into the a
B-tree structure, so there is no need for the columns to be adjacent.
Variable length columns are always separated from fixed length columns at
the physical layer, and depending on the option settings when the table is
created, nullable fixed-length character columns are treated in the same way
as variable-length character columns. In addition, SQL Server reads an
extent at a time, so there is no performance penalty for having intervening
columns within a row since the entire row is in memory anyway. Therefore,
it is pointless to try to force the columns to be in a specific order,
unless you're going to use SELECT *.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96CE72C7ED62Yazorman@.127.0.0.1...
> Ahmed Hashish (a_hashish@.hotmail.com) writes:
columns
> You are completely right, and Brian is wrong. There are several good
reasons
> why one want to have columns in a certain order, and SELECT * is not one
> of them. But have PK columns first in the table, and in the correct order
> is one. And wanting have logically related columns close to each other is
> another.
> Anyway, there is no syntax for this, not even in SQL 2005. What Enterprise
> Manager does is to create a new table, and then move over data to that
> table, and recreate foreign keys etc. In a script, I should add, that has
> several serious flaws. While these are fairly easy to address, an ALTER
> TABLE command would be easier to use.
> On MSDN Product Feedback Centre you can submit bugs and suggestions for
> SQL 2005, and then other people can vote on these submissions. One
> suggestion that was submitted earlier this year, was precisely about this
> matter. By now, it has assembled 21 votes, which I think makes it the
> most voted-on suggestion for SQL 2005. So you are not the first one to
> ask for this.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||> I also like to start the table definition with the columns of the
> Primary Key, preferably in the order of the Primary Key definition.
Sure, when you're first designing the table, I think we all tend to create
the column structure in a logical order.
Whether it is a logical thing to do after the table has been created, I'm
not so sure about that. Why would we be adding the primary key to the table
as an afterthought?
More often than not, this request seems to stem from issues like "I want all
my numeric columns together" or "I want such and such column at the 'end' of
the table."|||Brian Selzer (brian@.selzer-software.com) writes:
> The location of columns in a table is immaterial either from a
> performance standpoint, or for any other reason. A primary key
> constraint always creates an index, which means that the key values are
> copied into the a B-tree structure, so there is no need for the columns
> to be adjacent. Variable length columns are always separated from fixed
> length columns at the physical layer, and depending on the option
> settings when the table is created, nullable fixed-length character
> columns are treated in the same way as variable-length character
> columns. In addition, SQL Server reads an extent at a time, so there is
> no performance penalty for having intervening columns within a row since
> the entire row is in memory anyway. Therefore, it is pointless to try
> to force the columns to be in a specific order, unless you're going to
> use SELECT *.
If only computers were reading the table, you would be right. Almost, more
a little later.
But the table is also used by people. If you are going to develop something
in a database, it may be perfectly OK to you if you look at the table
definition and the columns appear in the order they were added to the table.
Personally, I prefer to see column in a logical order, for instance PK
column first, auditing columns at the end, and related column adjancent
to each other.
This also matters when you do a SELECT * from Query Analyzer for debugging
reasons. Which I do a lot. (SELECT * in code is another matter.)
There is also a technical reason. Say that your column order is accidental,
and now you are to bulk load out from a table one server to the same table
on another server. If you know about it, you write a format file (jolly
good fun for a 100-column table!). If you don't know about it, you may
be informed of errors. Then again you may not, because the colunms that
were in different order were of the same data types.
Column order is not about performance. It's about usability.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Showing posts with label alter. Show all posts
Showing posts with label alter. Show all posts
Thursday, March 8, 2012
Saturday, February 25, 2012
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...
> > 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.)
>|||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...
>> 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
>>
>|||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...
> > 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
> >>
> >>
> >
> >
>|||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...
>> 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...
>> >> 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
>> >>
>> >>
>> >
>> >
>>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_001D_01C4D4C1.127F3640
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Anthony,
It's arguable that DBMSs would be better if they stayed closer to the =relational model, but since column ordinal positions are part of the =ANSI SQL standard, I think it's appropriate to provide that attribute of =a column in metadata. 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 while 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 order of their (virtual, and stored =in metadata, not physical) ordinal position.
SQL Server's column ordinal positions are not an exposed physical =characteristic 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 row, since it can be beneficial to know =for troubleshooting, design, optimization, and so on. But no T-SQL =language constructs exist to access the information that way, save =perhaps for some undocumented DBCC commands. Fixed-length columns are =stored before variable-length columns, for example (regardless 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 =INFORMATION_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 page, 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 exposed 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 if at all possible.
Steve Kass
Drew University
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message =news:OZecUbL1EHA.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 order 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 =physical characteristics that should have been shielded from =end-users...even Database Administrators, Engineers, and Developers. =This is just another case where 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 this is meaningless. Now, you can affect the outcome but, as =the other respondents have said, you must drop and recreate or create a =temp table, migrate the data, drop the original, and, then, rename the =temp. This is how the Visual Database designer does it.
Sincerely,
Anthony Thomas
-- "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
--=_NextPart_000_001D_01C4D4C1.127F3640
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Anthony,
It's arguable that DBMSs would be better if =they stayed closer to the relational model, but since column ordinal positions are =part of the ANSI SQL standard, I think it's appropriate to provide that =attribute of a column in metadata. 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 while 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 order of their (virtual, and stored in metadata, not =physical) ordinal position.
SQL Server's column ordinal positions are not an =exposed physical characteristic 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 row, since it can be beneficial to =know for troubleshooting, design, optimization, and so on. But no T-SQL =language constructs exist to access the information that way, save perhaps for =some undocumented DBCC commands. Fixed-length columns are stored before =variable-length columns, for example (regardless 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 INFORMATION_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 page, 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 exposed 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 if at all possible.
Steve Kass
Drew University
"AnthonyThomas" wrote in message news:OZecUbL1EHA.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 order =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 physical =characteristics that should have been shielded from end-users...even Database =Administrators, Engineers, and Developers. This is just another case where 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 this is meaningless. Now, you can affect the outcome but, as the other respondents have said, you must drop and recreate or create a temp =table, migrate the data, drop the original, and, then, rename the temp. =This is how the Visual Database designer does it.
Sincerely,
Anthony = Thomas
--
"Leila" 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 liketo know if it is possible to force column order when I alter =a table to addcolumn or change the order later.It seems that the =orders are stored in syscolumns table but I think thereshould be a system =sp/func to alter that.Thanks,Leila
=
--=_NextPart_000_001D_01C4D4C1.127F3640--|||This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C4D6C0.C2567F50
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
I do like to have some semblance of order in column order, if only for =display 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 each other and in that order.
"Steve Kass" <skass@.drew.edu> wrote in message =news:eSW%230rO1EHA.2192@.TK2MSFTNGP14.phx.gbl...
Anthony,
It's arguable that DBMSs would be better if they stayed closer to =the relational model, but since column ordinal positions are part of the =ANSI SQL standard, I think it's appropriate to provide that attribute of =a column in metadata. 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 while 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 order of their (virtual, and stored =in metadata, not physical) ordinal position.
SQL Server's column ordinal positions are not an exposed physical =characteristic 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 row, since it can be beneficial to know =for troubleshooting, design, optimization, and so on. But no T-SQL =language constructs exist to access the information that way, save =perhaps for some undocumented DBCC commands. Fixed-length columns are =stored before variable-length columns, for example (regardless 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 =INFORMATION_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 page, 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 exposed 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 if at all possible.
Steve Kass
Drew University
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message =news:OZecUbL1EHA.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 order 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 =physical characteristics that should have been shielded from =end-users...even Database Administrators, Engineers, and Developers. =This is just another case where 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 this is meaningless. Now, you can affect the outcome but, as =the other respondents have said, you must drop and recreate or create a =temp table, migrate the data, drop the original, and, then, rename the =temp. This is how the Visual Database designer does it.
Sincerely,
Anthony Thomas
-- "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
--=_NextPart_000_0008_01C4D6C0.C2567F50
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I do like to have some =semblance of order in column order, if only for display 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 each other and in that order.
"Steve Kass" wrote in message news:eSW%230rO1EHA.=2192@.TK2MSFTNGP14.phx.gbl...
Anthony,
It's arguable that DBMSs would be better if =they stayed closer to the relational model, but since column ordinal =positions are part of the ANSI SQL standard, I think it's appropriate to =provide that attribute of a column in metadata. 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 while 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 order of their (virtual, and =stored in metadata, not physical) ordinal position.
SQL Server's column ordinal positions are not an =exposed physical characteristic 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 row, since it can be beneficial to =know for troubleshooting, design, optimization, and so on. But no =T-SQL language constructs exist to access the information that way, save =perhaps for some undocumented DBCC commands. Fixed-length columns are stored =before variable-length columns, for example (regardless 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 INFORMATION_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 page, 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 exposed 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 if at all possible.
Steve Kass
Drew University
"AnthonyThomas" wrote in message news:OZecUbL1EHA.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 order =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 physical characteristics that should have been shielded from end-users...even = Database Administrators, Engineers, and Developers. This is =just another case where 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 this =is meaningless. Now, you can affect the outcome but, as the other = respondents have said, you must drop and recreate or create a temp =table, migrate the data, drop the original, and, then, rename the =temp. This is how the Visual Database designer does it.
Sincerely,
Anthony Thomas
--
"Leila" 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 liketo know if it is possible to force column order when I =alter a table to addcolumn or change the order later.It seems that =the orders are stored in syscolumns table but I think thereshould =be a system sp/func to alter =that.Thanks,Leila
--=_NextPart_000_0008_01C4D6C0.C2567F50--|||>>
I do like to have some semblance of order in column order, if only for
display 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 each other and in that order.
Okay, so if you build the table and somehow forget to include the address
column, then drop the table and re-create it. They're not going to change
the ALTER TABLE command for cosmetics.
--
http://www.aspfaq.com/
(Reverse address to reply.)
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...
> > 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.)
>|||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...
>> 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
>>
>|||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...
> > 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
> >>
> >>
> >
> >
>|||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...
>> 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...
>> >> 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
>> >>
>> >>
>> >
>> >
>>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_001D_01C4D4C1.127F3640
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Anthony,
It's arguable that DBMSs would be better if they stayed closer to the =relational model, but since column ordinal positions are part of the =ANSI SQL standard, I think it's appropriate to provide that attribute of =a column in metadata. 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 while 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 order of their (virtual, and stored =in metadata, not physical) ordinal position.
SQL Server's column ordinal positions are not an exposed physical =characteristic 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 row, since it can be beneficial to know =for troubleshooting, design, optimization, and so on. But no T-SQL =language constructs exist to access the information that way, save =perhaps for some undocumented DBCC commands. Fixed-length columns are =stored before variable-length columns, for example (regardless 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 =INFORMATION_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 page, 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 exposed 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 if at all possible.
Steve Kass
Drew University
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message =news:OZecUbL1EHA.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 order 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 =physical characteristics that should have been shielded from =end-users...even Database Administrators, Engineers, and Developers. =This is just another case where 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 this is meaningless. Now, you can affect the outcome but, as =the other respondents have said, you must drop and recreate or create a =temp table, migrate the data, drop the original, and, then, rename the =temp. This is how the Visual Database designer does it.
Sincerely,
Anthony Thomas
-- "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
--=_NextPart_000_001D_01C4D4C1.127F3640
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Anthony,
It's arguable that DBMSs would be better if =they stayed closer to the relational model, but since column ordinal positions are =part of the ANSI SQL standard, I think it's appropriate to provide that =attribute of a column in metadata. 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 while 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 order of their (virtual, and stored in metadata, not =physical) ordinal position.
SQL Server's column ordinal positions are not an =exposed physical characteristic 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 row, since it can be beneficial to =know for troubleshooting, design, optimization, and so on. But no T-SQL =language constructs exist to access the information that way, save perhaps for =some undocumented DBCC commands. Fixed-length columns are stored before =variable-length columns, for example (regardless 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 INFORMATION_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 page, 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 exposed 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 if at all possible.
Steve Kass
Drew University
"AnthonyThomas" wrote in message news:OZecUbL1EHA.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 order =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 physical =characteristics that should have been shielded from end-users...even Database =Administrators, Engineers, and Developers. This is just another case where 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 this is meaningless. Now, you can affect the outcome but, as the other respondents have said, you must drop and recreate or create a temp =table, migrate the data, drop the original, and, then, rename the temp. =This is how the Visual Database designer does it.
Sincerely,
Anthony = Thomas
--
"Leila"
=
--=_NextPart_000_001D_01C4D4C1.127F3640--|||This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C4D6C0.C2567F50
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
I do like to have some semblance of order in column order, if only for =display 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 each other and in that order.
"Steve Kass" <skass@.drew.edu> wrote in message =news:eSW%230rO1EHA.2192@.TK2MSFTNGP14.phx.gbl...
Anthony,
It's arguable that DBMSs would be better if they stayed closer to =the relational model, but since column ordinal positions are part of the =ANSI SQL standard, I think it's appropriate to provide that attribute of =a column in metadata. 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 while 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 order of their (virtual, and stored =in metadata, not physical) ordinal position.
SQL Server's column ordinal positions are not an exposed physical =characteristic 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 row, since it can be beneficial to know =for troubleshooting, design, optimization, and so on. But no T-SQL =language constructs exist to access the information that way, save =perhaps for some undocumented DBCC commands. Fixed-length columns are =stored before variable-length columns, for example (regardless 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 =INFORMATION_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 page, 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 exposed 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 if at all possible.
Steve Kass
Drew University
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message =news:OZecUbL1EHA.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 order 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 =physical characteristics that should have been shielded from =end-users...even Database Administrators, Engineers, and Developers. =This is just another case where 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 this is meaningless. Now, you can affect the outcome but, as =the other respondents have said, you must drop and recreate or create a =temp table, migrate the data, drop the original, and, then, rename the =temp. This is how the Visual Database designer does it.
Sincerely,
Anthony Thomas
-- "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
--=_NextPart_000_0008_01C4D6C0.C2567F50
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I do like to have some =semblance of order in column order, if only for display 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 each other and in that order.
"Steve Kass"
Anthony,
It's arguable that DBMSs would be better if =they stayed closer to the relational model, but since column ordinal =positions are part of the ANSI SQL standard, I think it's appropriate to =provide that attribute of a column in metadata. 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 while 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 order of their (virtual, and =stored in metadata, not physical) ordinal position.
SQL Server's column ordinal positions are not an =exposed physical characteristic 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 row, since it can be beneficial to =know for troubleshooting, design, optimization, and so on. But no =T-SQL language constructs exist to access the information that way, save =perhaps for some undocumented DBCC commands. Fixed-length columns are stored =before variable-length columns, for example (regardless 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 INFORMATION_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 page, 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 exposed 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 if at all possible.
Steve Kass
Drew University
"AnthonyThomas" wrote in message news:OZecUbL1EHA.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 order =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 physical characteristics that should have been shielded from end-users...even = Database Administrators, Engineers, and Developers. This is =just another case where 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 this =is meaningless. Now, you can affect the outcome but, as the other = respondents have said, you must drop and recreate or create a temp =table, migrate the data, drop the original, and, then, rename the =temp. This is how the Visual Database designer does it.
Sincerely,
Anthony Thomas
--
"Leila"
--=_NextPart_000_0008_01C4D6C0.C2567F50--|||>>
I do like to have some semblance of order in column order, if only for
display 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 each other and in that order.
Okay, so if you build the table and somehow forget to include the address
column, then drop the table and re-create it. They're not going to change
the ALTER TABLE command for cosmetics.
--
http://www.aspfaq.com/
(Reverse address to reply.)
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...[vbcol=seagreen]
> like
> add
> Only by dropping the table and re-creating it.
there
> 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[vbcol=seagreen]
> 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
>
|||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 order 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 physical characteristics that should have been shielded from end-users...even Database Administrators, Engineers, and Developers. This is just another case where 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 this is meaningless. Now, you can affect the outcome but, as the other respondents have said, you must drop and recreate or create a temp table, migrate the data, drop the original, and, then, rename the temp. This is how the Visual Database designer does it.
Sincerely,
Anthony Thomas
"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
|||Anthony,
It's arguable that DBMSs would be better if they stayed closer to the relational model, but since column ordinal positions are part of the ANSI SQL standard, I think it's appropriate to provide that attribute of a column in metadata. 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 while 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 order of their (virtual, and stored in metadata, not physical) ordinal position.
SQL Server's column ordinal positions are not an exposed physical characteristic 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 row, since it can be beneficial to know for troubleshooting, design, optimization, and so on. But no T-SQL language constructs exist to access the information that way, save perhaps for some undocumented DBCC commands. Fixed-length columns are stored before variable-length columns, for example (regardless 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 INFORMATION_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 page, 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 exposed 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 if at all possible.
Steve Kass
Drew University
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message news:OZecUbL1EHA.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 order 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 physical characteristics that should have been shielded from end-users...even Database Administrators, Engineers, and Developers. This is just another case where 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 this is meaningless. Now, you can affect the outcome but, as the other respondents have said, you must drop and recreate or create a temp table, migrate the data, drop the original, and, then, rename the temp. This is how the Visual Database designer does it.
Sincerely,
Anthony Thomas
"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
|||I do like to have some semblance of order in column order, if only for display 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 each other and in that order.
"Steve Kass" <skass@.drew.edu> wrote in message news:eSW%230rO1EHA.2192@.TK2MSFTNGP14.phx.gbl...
Anthony,
It's arguable that DBMSs would be better if they stayed closer to the relational model, but since column ordinal positions are part of the ANSI SQL standard, I think it's appropriate to provide that attribute of a column in metadata. 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 while 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 order of their (virtual, and stored in metadata, not physical) ordinal position.
SQL Server's column ordinal positions are not an exposed physical characteristic 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 row, since it can be beneficial to know for troubleshooting, design, optimization, and so on. But no T-SQL language constructs exist to access the information that way, save perhaps for some undocumented DBCC commands. Fixed-length columns are stored before variable-length columns, for example (regardless 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 INFORMATION_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 page, 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 exposed 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 if at all possible.
Steve Kass
Drew University
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message news:OZecUbL1EHA.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 order 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 physical characteristics that should have been shielded from end-users...even Database Administrators, Engineers, and Developers. This is just another case where 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 this is meaningless. Now, you can affect the outcome but, as the other respondents have said, you must drop and recreate or create a temp table, migrate the data, drop the original, and, then, rename the temp. This is how the Visual Database designer does it.
Sincerely,
Anthony Thomas
"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
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...[vbcol=seagreen]
> like
> add
> Only by dropping the table and re-creating it.
there
> 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[vbcol=seagreen]
> 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
>
|||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 order 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 physical characteristics that should have been shielded from end-users...even Database Administrators, Engineers, and Developers. This is just another case where 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 this is meaningless. Now, you can affect the outcome but, as the other respondents have said, you must drop and recreate or create a temp table, migrate the data, drop the original, and, then, rename the temp. This is how the Visual Database designer does it.
Sincerely,
Anthony Thomas
"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
|||Anthony,
It's arguable that DBMSs would be better if they stayed closer to the relational model, but since column ordinal positions are part of the ANSI SQL standard, I think it's appropriate to provide that attribute of a column in metadata. 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 while 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 order of their (virtual, and stored in metadata, not physical) ordinal position.
SQL Server's column ordinal positions are not an exposed physical characteristic 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 row, since it can be beneficial to know for troubleshooting, design, optimization, and so on. But no T-SQL language constructs exist to access the information that way, save perhaps for some undocumented DBCC commands. Fixed-length columns are stored before variable-length columns, for example (regardless 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 INFORMATION_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 page, 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 exposed 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 if at all possible.
Steve Kass
Drew University
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message news:OZecUbL1EHA.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 order 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 physical characteristics that should have been shielded from end-users...even Database Administrators, Engineers, and Developers. This is just another case where 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 this is meaningless. Now, you can affect the outcome but, as the other respondents have said, you must drop and recreate or create a temp table, migrate the data, drop the original, and, then, rename the temp. This is how the Visual Database designer does it.
Sincerely,
Anthony Thomas
"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
|||I do like to have some semblance of order in column order, if only for display 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 each other and in that order.
"Steve Kass" <skass@.drew.edu> wrote in message news:eSW%230rO1EHA.2192@.TK2MSFTNGP14.phx.gbl...
Anthony,
It's arguable that DBMSs would be better if they stayed closer to the relational model, but since column ordinal positions are part of the ANSI SQL standard, I think it's appropriate to provide that attribute of a column in metadata. 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 while 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 order of their (virtual, and stored in metadata, not physical) ordinal position.
SQL Server's column ordinal positions are not an exposed physical characteristic 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 row, since it can be beneficial to know for troubleshooting, design, optimization, and so on. But no T-SQL language constructs exist to access the information that way, save perhaps for some undocumented DBCC commands. Fixed-length columns are stored before variable-length columns, for example (regardless 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 INFORMATION_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 page, 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 exposed 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 if at all possible.
Steve Kass
Drew University
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message news:OZecUbL1EHA.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 order 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 physical characteristics that should have been shielded from end-users...even Database Administrators, Engineers, and Developers. This is just another case where 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 this is meaningless. Now, you can affect the outcome but, as the other respondents have said, you must drop and recreate or create a temp table, migrate the data, drop the original, and, then, rename the temp. This is how the Visual Database designer does it.
Sincerely,
Anthony Thomas
"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
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
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
Subscribe to:
Posts (Atom)