Posted by Hugo Kornelis on 06/08/05 00:24

On Mon, 6 Jun 2005 22:31:07 +0000 (UTC), Erland Sommarskog wrote:

>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.

Hi Erland,

I don't have those difficulties, but if you do, then I can see that
you'd rather avoid this syntax. But I do not agree that posting this
code amounts to "poor advice".

You could even defend the opposite: it's better that people get their
first glimpse of that syntax here, where they can ask questions about
it, then while debugging some poorly documented code in a
mission-critical database.

> You and
>I may know the alias rules by heart, but not everyone may feel fully
>confident in them.

Yes, I agree that the ANSI compliant UPDATE statement could be confusing
for inexperienced SQL Server users. But the T-SQL UPDATE statement comes
with a much bigger risk in the hands of less experienced people: a small
mistake in the subquery could easily result in inpredictabe results,
that might be correct in test, but wrong in production - without any
warning from SQL Server!! The ANSI compliant version with the same
mistake would throw an error, forcing the programmer or DBA to review
the code.


(snip)
> because some purist put ANSI compliance ahead of
>clarity.

I am not a purist. In performance-critical parts of my DB's, I use what
performs best. In other parts, I use ANSI, unless I feel that there is a
huge gain in readability and maintainability (e.g. when more columns
have to be set, I generally prefer a joined UPDATE over repeating the
same subquery over and over again).

Let's agree to disagree on the readability of ANSI-standard UPDATE
statements, but let's also agree to not call each other names over this,
okay?


Another difference of opinion:

>> 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.

This exception is exactly the reason why I always use the alias after
the UPDATE or DELETE keyword - if you have to do it in some cases,
better be consequent and do it in all cases. Besides, it's a good
reminder that the T-SQL version of the UPDATE / DELETE syntax is used!


>> 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?

You've got me there! :)

What I meant to write was: document it when you CHOOSE to use T-SQL
instead of ANSI SQL. String concatenation, date and time handling, temp
tables and lots of other things can only be done with proprietary code,
so there's no need to document them. UPDATE FROM and DELETE FROM can
always be replaced by ANSI-compliant code, just as code that uses the
TOP keyword - those are the cases that I always document.

(snip parts I agree with)

>>>(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.

I'll counter-speculate that machines would have far less trouble with
this than humans. If an RDBMS has trouble with this statement, then my
guess is that it either has trouble with all correlated subqueries in
update statements (whether with or without alias), or that is has
trouble with all queries that mix aliased and non-aliased tables. In
both cases, the product would disqualify as serious DB in my book.

(snip parts I agree with)

>> 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?

No, that's not what I meant. A good DB uses standardized code in most
cases (and in our case, your standard for UPDATE statements is different
from mine - but as long as each of us sticks to his own standard, that's
fine <g>). But for the long-running performance-hurting queries and
statements, we pull every trick that we know, in order to squeeze the
last bit of performance out of it - if necessary even at the expense of
standard constructions, readability and maintainability.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Back to original message]