Posted by Erland Sommarskog on 06/07/05 01:31

Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
> In this case, though, you're missing the point. The update statement
> above is completely non-ambiguous, and that has nothing to do with any
> wording in ANSI. It has to do with how table aliases work in SQL Server.

Yes, I know that there are such rules. But my main point is that I as
a human have a difficult to understand what the query means. You and
I may know the alias rules by heart, but not everyone may feel fully
confident in them. Something is not working properly, and the support
person freaks out on this query, thinking this must be where the error
is, and wastes time, because some purist put ANSI compliance ahead of
clarity.

By the way, I think these rules were introduced in 6.5, but I don't know
what would have happened with this statement in 6.0 or 4.x.

> Use the alias after the UPDATE or DELETE keyword, not the table name.

I much prefer to use the table name, since at this point the reader may
not know what "a" stands for. There is one exception, and that is if
you self-join against the table being updated/deleted to. If you are on
SQL 7 or 6.5 it's a different matter: here you need to use the alias,
as soon as you use the newer ANSI-join syntax.

> 4. In an ANSI UPDATE or DELETE with subquery (or subqueries), don't use
> an alias for the table to be updated or deleted (not allowed!), but do
> use aliases for all tables in the subqueries. Prefix ALL column names
> with either the apppropriate prefix, or the tablename of the table to be
> updated or deleted.

And I say: always use an alias, and add a FROM clause if only to
introduce an alias.

> 5. Use ANSI standard constructions, unless there is a good reason to
> choose proprietary SQL. Always document the reason for the use of
> proprietary SQL.

That would be a lot of comments. Do you really write a comment everytime
you use + for string concatenation? Every time you create a temp table?

Of course, if there is a clearly outspoken desire to be portable, then
there is a point with it. But in many most situations, portability is
not on the agenda. Being ANSI-compliant in such case only means that
you work with your RDBMS with one hand tied behind your back. And
if the day comes when you actually will have to target another engine,
you find that that nice ANSI-compliant syntax will have to be rewritten
anyway, because the new engine did not support that syntax anyway.

And even if the engine supports the syntax, you may find that the
performance did not port. You know, customers do not that make much
distinction between a syntax error and a query that runs longer than
their patience permits them.

>>(And I would
>>not be surprised if more than one engine gets lost on the query above,
>>so I would not even trust the elusive compatibility.)
>
> Since I know only SQL Server, I'll have to take your word for it.

Note that I didn't say that there are such engines. I only speculated
that there could be, since this is a case which really invites to bugs.

>>The ANSI standard does not provide a place to put in an alias, and
>>that is a serious shortcoming. In this case, it leads to the nonsese
>>above. In other cases, imagine that you have a table by the of
>>instrumentclearingmarketplaces - writing that over and over again
>>is completely out of the question.
>
> The same "problem" occurs with long column names. Using drag and drop
> from object editor, copy/paste, programmable macro keys and/or mnemonics
> while typing and search&replace when done are easy solutions.

No, the problem is not the same with long column names, because they
are not repeated over and over again over the query. And the problem
is not about typing the code. Again, it is about reading the code.

> You are very probably right - I have seen numerous occasions where the
> proprietary UPDATE FROM far outperformed the ANSI equivalent. If
> performance is the main objective, then UPDATE FROM might be the better
> choice. But performance is not always the main objective. If the tables
> are small, then nobody will care if it takes 3 ms or 6 ms.

So we should use one syntax for small tables, and one for big tables?
What if that small table becomes big one day? Frankly, that is far more
likely that the code will be ported.

Most production databases have considerable amounts of data, but
development environments are offer lesser-scale, so developers are
not always directly made aware of performance issues. Better to learn
them to use something that provides best performance in most cases.

I remember one such stored procedure where we slashed execition time
from 4 minutes to a couple of seconds by replacing correlated subqueries
with derived tables in two update statments.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]