Column Order in Select * Statement - Guaranteed

column order in SELECT * statement - guaranteed?

Let's consider the SQL standard, section 7.9 <query specification> as specified here:

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

<query specification> ::=
          SELECT [ <set quantifier> ] <select list> <table expression>
[...]
<select list> ::=
            <asterisk>
          | <select sublist> [ { <comma> <select sublist> }... ]

[...]
Syntax Rules
1) Let T be the result of the <table expression>.
3) Case:
       a) [...]
       b) Otherwise, the <select list> "*" is equivalent to a <value
          expression> sequence in which each <value expression> is a
          <column reference> that references a column of T and each
          column of T is referenced exactly once. The columns are ref-
          erenced in the ascending sequence of their ordinal position
          within T.

So, in other words, yes, the SQL standard specifies that columns are to be projected according to their ordinal position within T. Note, that things get a bit tricky, when your <table expression> consists of several tables involving JOIN .. USING or NATURAL JOIN clauses. However, when selecting from a simple table, you're probably fine assuming that the order is as expected.

For completeness, the meaning of an ordinal position within T for tables is explained further down in 11.4 <column definition>:

General Rules
     5) [...] The ordinal position included
        in the column descriptor is equal to the degree of T. [...]

And then in 11.11 <add column definition> (for ALTER TABLE statements)

General Rules
     4) [...] In particular, the degree of T
        is increased by 1 and the ordinal position of that column is
        equal to the new degree of T as specified in the General Rules
        of Subclause 11.4, "<column definition>".

There are quite a few other SQL statements and clauses that depend on the formal specification of ordinal positions within <table expressions>. Some examples:

13.8 <insert statement> 
     (when omitting the `<insert column list>`)
20.2 <direct select statement: multiple rows>
     (when `<sort specification>` contains an `<unsigned integer>`)

Postgres, in particular, is quite standards-compliant, so if you really want to SELECT *, go ahead!

Does order by in view guarantee order of select?

You can't count on the order of rows in any query that doesn't have an explicit ORDER BY clause. If you query an ordered view, but you don't include an ORDER BY clause, be pleasantly surprised if they're in the right order, and don't expect it to happen again.

That's because the query optimizer is free to access rows in different ways depending on the query, table statistics, row counts, indexes, and so on. If it knows your query doesn't have an ORDER BY clause, it's free to ignore row order in order (cough) to return rows more quickly.

Slightly off-topic . . .

Sort order isn't necessarily identical across platforms even for well-known collations. I understand that sorting UTF-8 on Mac OS X is particularly odd. (PostgreSQL developers call it broken.) PostgreSQL relies on strcoll(), which I understand relies on the OS locales.

It's not clear to me how PostgreSQL 9.1 will handle this. In 9.1, you can have multiple indexes, each with a different collation. An ORDER BY that doesn't specify a collation will usually use the collation of the underlying base table's columns, but what will the optimizer do with an index that specifies a different collation than an unindexed column in the base table?

How does SQL Server determine the order of the columns when you do a `SELECT *`?

They are in the order of column_id from the system view sys.columns.

You can check it by:

SELECT column_id, name
FROM sys.columns
WHERE object_id = Object_id('MyTableName')
ORDER BY column_id

EDIT

This is for Dems. You should test on a larger table, but it looks like it uses the order defined in the table, not the index:

CREATE TABLE #T (cola int, colb int, colc int)

INSERT INTO #T
VALUES
(1,2,3),
(2,3,4),
(4,5,6)

SELECT * FROM #T

CREATE INDEX ix_test ON #T (colb, colc, cola)

SELECT * FROM #t
WHERE colb > 0

DROP TABLE #T

How can I keep the order of column values in a union select?

If you're not selecting from tables?

Then you could insert VALUES, instead of a select with unions.

insert into @QuestionOptionMapping (RateCode) values
 ('PD0116')
,('PL0090')
,('PL0091')
,('DD0026')
,('DD0025')

Or in your query, change all the UNION to UNION ALL.

The difference between a UNION and a UNION ALL is that a UNION will remove duplicate rows.
While UNION ALL just stiches the resultsets from the selects together.

And for UNION to find those duplicates, internally it first has to sort them.

But a UNION ALL doesn't care about uniqueness, so it doesn't need to sort.

A 3th option would be to simply change from 1 insert statement to multiple insert statements.
One insert per value. Thus avoiding UNION completely.

But that anti-golfcoding method is also the most wordy.

Is the output order of VALUES clause guaranteed to be the same as the order the elements are mentioned

No. There's no such guarantee. SQL is a set-based language, where {1,3} and {3,1} are the same thing.

The only place you get guarantees on ordering is when you use an ORDER BY clause that unambiguously specifies an ordering of the values you're working with.

Since your ORDER BY depends on a ROW_NUMBER() expression that, in turn, uses an ORDER BY which is completely ambiguous, no ordering guarantees exist.

---

Related Topics