Re: TIP#308: Last serial column value generated

tom.rmadilo wrote:
On Dec 3, 7:53 pm, Darren New <d...@xxxxxxxxxx> wrote:
tom.rmadilo wrote:
I would also like to note that Kevin's example demonstrates that SQL
queries produce column ordered results,
I don't think anyone disputed that. IIRC, it was more an academic
discussion of whether preserving column order was part of the relational
model or not.

You didn't appear to be interested in an academic discussion, I wrote:

Where did I say that SQL doesn't return ordered fields? Nobody disputed that.

Maybe even
write some regular SQL code so that you understand that select
statements produce ordered fields.

And you responded:

I've been doing database stuff since oracle cost $100K a seat.

Pointing out that I've written quite a lot of regular SQL code, and that I understand select statements produce ordered fields.

I've
never relied on the order of columns in a result set.

A personal style, not a dispute that columns aren't ordered.

The relational
model doesn't rely on the order of columns in a result set.

What I said above. SQL != relational model.

Every
tutorial I've ever seen says it's bad form to rely on the order of
columns in a result set, especially with SELECT *.

This is true.

Doing SELECT * obviously gives you the current order of the columns.
The order is fixed, until someone does a ddl which may reorder the
columns. Then they again remain fixed.

Correct. So? Where does that disagree with anything I've said?

BTW, it's the fact that the column order *can* change that makes me use named columns. It's why I use variables like "number_of_users" instead of "X9" also.

This is a distinct concept from
relying on the order to remain the same as the database structure is
manipulated. But between ddl manipulations, the column order is always
the same.

I didn't dispute that.

And obviously if you specify columns, you are relying on the
order of the columns in the result set.

You're also limiting the ability to pass things through layers of libraries, to some extent, in the same sense that strong typing without OO-style classification can limit the usability of libraries.

For example, if every SELECT I do lists all the columns, then I can no longer add a column to a table, populate it, and have it automatically show up in reports. The once-and-only-once bit becomes much harder to maintain, because you have to start querying the database to see what columns are available if you want to do something generic, instead of just using "*".

For example, I recently had a routine where I had SQL of the form
proc fetchrows {tag {tables tagtable} {extra ""}} {
...
set sql "SELECT * FROM $tables WHERE tagtable.tag = '$tag' $extra"
...
return $resultset
}

This got used in several routines. The $tables and $extra let me join the tagtable with other tables as needed at the time. At one point, I needed the "ham" column off one of the other tables in the $tables list, and I spent a few minutes trying to figure out how to get it, whether it would be efficient to look it up again, where would be appropriate in the call chain, until I actually followed the code all the way back to fetchrows and discovered I'd already fetched the appropriate ham. I eliminated changing a routine used in a number of places, and simply made use of extra data, which wouldn't have worked had I not used *.

Yeah, I could have passed in the list of columns to return, too, but why? It's not noticably more efficient (certainly parsing vs transmission of the data for one or two rows isn't important, or at least better not be), and I'd have to test that other pieces of code that used the same results didn't break.

Your statement above indicates you think this is bad form.

Bad form, or at least not my style.

I've also provided a number of examples where ordinal numbers are used
in queries where the column is a function. SQL does not define names
for these columns. If you couldn't rely on the order of columns, SQL
would not work except for very simple operations. But even very simple
operations rely on this:

INSERT into table_x values (...);

This is a very reliable feature of SQL.

I didn't dispute that. Nor did I suggest that ordinal columns shouldn't be supported. I merely disputed that named columns should be excluded.

Again, where did I say columns aren't ordered in SQL? I said they're not ordered in the relational model, and I never relied on them being ordered.

--
Darren New / San Diego, CA, USA (PST)
It's not feature creep if you put it
at the end and adjust the release date.
.

Relevant Pages

  • Indexed Views - What edition????
    ... I am in a big dispute with my boss of indexed views. ... can only be created in SQL 2000 Enterprise Edition. ... Places we checked were TechNet, MSDN, and SQL Server Home Page. ...
    (microsoft.public.sqlserver.programming)
  • Re: Official Status of SQLServer 2005 ADP
    ... I have said that the support for SQL passthrough ... queries under MDB was bad and worst than the one offered by ADP while you ... > attempt to "pass through" every Access query against a linked ODBC ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Massive Large Query Issues
    ... Project Budgets: Entered at the Capital Request level, ... installed (One install is typically $100,000 plus the cost of the equpment, ... install SQL Server. ... Often, a collection of queries ...
    (microsoft.public.access.queries)
  • Re: Massive Large Query Issues
    ... Project Budgets: Entered at the Capital Request level, ... install SQL Server. ... of queries? ... make of the query you provided is to roll it up into a subsequent ...
    (microsoft.public.access.queries)
  • Re: Dynamic query problem
    ... On Oct 17, 9:41 am, Andy Hull ... If we were to provide a fully featured dynamic query generator we would have ... Provided with already built queries which they can edit ... SQL and see where it is the same as for the other queries and where it ...
    (microsoft.public.access.queries)
Loading