CREATE TABLE and column order

I create a table by sending a CREATE TABLE command to the database. The
create is successful but when I look at the table in Enterprise Manager
the order of the columns is in alphabetic order and not in the order I
specified when I issued the CREATE TABLE command. Have the columns
really been created in the order in which I see them under Enterprise
Manager ? If so, how do I enforce that the order of the columns is the
same as the order I specify when I created the table ?"Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
news:#Ygoih6XFHA.796@.TK2MSFTNGP09.phx.gbl...
> I create a table by sending a CREATE TABLE command to the database. The
> create is successful but when I look at the table in Enterprise Manager
> the order of the columns is in alphabetic order and not in the order I
> specified when I issued the CREATE TABLE command. Have the columns
> really been created in the order in which I see them under Enterprise
> Manager ? If so, how do I enforce that the order of the columns is the
> same as the order I specify when I created the table ?
Edward,
Does it really matter what order the columns are in? On the data page
itself, the column are put in to an order that SQL Server specifies complete
with headers on each row, null and varchar bitmaps for the row data, and
then the actual row data. If you are using blob objects (text, ntext,
image) then they don't necessarily even live in the row itself, but have
16-byte pointers to other data pages.
To ensure that your data is SELECTed INSERTed and UPDATEed properly, ensure
that you specify a column list in these statements.
Rick Sawtell
MCT, MCSD, MCDBA|||Edward
Are you sure?
I did small test
CREATE TABLE Test8
(
B INT,
A INT
)
Looking in EM I see the same order
Why the order of the columns is so important for you?
When you perform SELECT statement you can specify any order of columns.
"Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
news:%23Ygoih6XFHA.796@.TK2MSFTNGP09.phx.gbl...
> I create a table by sending a CREATE TABLE command to the database. The
> create is successful but when I look at the table in Enterprise Manager
> the order of the columns is in alphabetic order and not in the order I
> specified when I issued the CREATE TABLE command. Have the columns
> really been created in the order in which I see them under Enterprise
> Manager ? If so, how do I enforce that the order of the columns is the
> same as the order I specify when I created the table ?|||You can create a view on the table specifying the order on your own.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> schrieb im
Newsbeitrag news:%23Ygoih6XFHA.796@.TK2MSFTNGP09.phx.gbl...
>I create a table by sending a CREATE TABLE command to the database. The
>create is successful but when I look at the table in Enterprise Manager the
>order of the columns is in alphabetic order and not in the order I
>specified when I issued the CREATE TABLE command. Have the columns really
>been created in the order in which I see them under Enterprise Manager ? If
>so, how do I enforce that the order of the columns is the same as the order
>I specify when I created the table ?|||Even though tables in relational databases do not have a "column order"
associated with them, SQL often associates positional significance to the
order of columns in a table. However except in a few circumstances, such
significance of the column order offer little or no benefits.
Not necessarily. To find the order of columns in t-SQL, you can query the
metadata and verify the ORDINAL_POSITION column like:
EXEC sp_columns tbl
Anith|||Rick Sawtell wrote:
> "Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
> news:#Ygoih6XFHA.796@.TK2MSFTNGP09.phx.gbl...
>
>
> Edward,
> Does it really matter what order the columns are in?
Very much so. Are you telling me that I can not ensure the column order
in SQL Server when I create a table ?

> On the data page
> itself, the column are put in to an order that SQL Server specifies comple
te
> with headers on each row, null and varchar bitmaps for the row data, and
> then the actual row data.
When you say "the data page", to what are you referring ?

> If you are using blob objects (text, ntext,
> image) then they don't necessarily even live in the row itself, but have
> 16-byte pointers to other data pages.
> To ensure that your data is SELECTed INSERTed and UPDATEed properly, ensur
e
> that you specify a column list in these statements.
That is not the issue. I need to ensure the actual order of columns is
the same as what I specified when I created the table. Is this the case,
and Enterprise Manager is not showing me the actual column order ? Or is
it the case that SQL Server actually changes the column order from what
I specified when I created the table ? The latter would be terrible.|||Anith Sen wrote:
> Even though tables in relational databases do not have a "column order"
> associated with them, SQL often associates positional significance to the
> order of columns in a table. However except in a few circumstances, such
> significance of the column order offer little or no benefits.
>
>
> Not necessarily. To find the order of columns in t-SQL, you can query the
> metadata and verify the ORDINAL_POSITION column like:
> EXEC sp_columns tbl
>
Sorry, this does work but the order of columns is not what I specified
when I created the table. SQL Server has moved the order of columns.
This is really horrible. There must be some way to ensure that the order
of columns in the table is the same as what I specified when I created
the table.|||Uri Dimant wrote:
> Edward
> Are you sure?
> I did small test
> CREATE TABLE Test8
> (
> B INT,
> A INT
> )
> Looking in EM I see the same order
Try adding primary keys not on the first column.

> Why the order of the columns is so important for you?
I am migrating data from one RDBMS to SQL Server. It is much easier if
the column order is the same in the from and to tables.
> When you perform SELECT statement you can specify any order of columns.
>
>
> "Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
> news:%23Ygoih6XFHA.796@.TK2MSFTNGP09.phx.gbl...
>|||Did you specify the correct table name in place of "tbl"?
Note that the column returned as ORDINAL_POSITION by sp_columns does
NOT necessarily reflect the order in which the columns were defined. If
you insert columns with EM then the table is recreated and you may not
see the result you expect. Also, this behaviour is subject to change in
future versions because EM is going away. Don't rely on it.
David Portas
SQL Server MVP
--|||Edward wrote on Mon, 23 May 2005 11:41:44 -0400:

> Anith Sen wrote:
> Running this stored procedure under SQL Server 7 Query Analyzer I get no
> rows returned.
Check you are putting your own table name in place of tbl, and you are in
the right database. Works fine here on my SQL 7 and SQL 2K servers.
Dan