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" <***@drew.edu> wrote in message news:eSW%***@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" <***@CommerceBank.com> wrote in message news:***@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" <***@hotpop.com> wrote in message news:***@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