|
|
Posted by Hugo Kornelis on 06/16/05 01:21
Hi Erland,
Sorry for picking up an old discussion. I've been planning to respond to
this the whole week, but other things just kept interfering. And yet, I
do want to respond to some of your points.
On Tue, 7 Jun 2005 23:11:31 +0000 (UTC), Erland Sommarskog wrote:
>Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
>> 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.
>
>Not all people read the newsgroups or other SQL forums. In facr, I would
>suspect most people don't.
But since this started over what advice I should or should not give in
the newsgroups, it's safe to assume that the people reading my advice do
read the groups.
>> 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!!
>
>That's another red herring. With the same argument you should use
>JOIN clauses in your SELECT either, but always get your values with
>subqueries in the column list.
No. JOIN clauses might result in undesired results (but so could
subqueries, or any other code), but not in unpredictable code. The
UPDATE FROM syntax and TOP without ORDER BY are the only constructions
where the result on the same starting data might change if the optimizer
picks a different plan.
>> The ANSI compliant version with the same mistake would throw an error,
>> forcing the programmer or DBA to review the code.
>
>Considet:
>
> UPDATE tbl
> SET col = (SELECT SUM(o.somecol)
> FROM othertbl o
> JOIN tbl t WHERE t.keycol = o.keycol)
>
>Exactly which error will be raised here?
None, as this is not the kind of mistake I refered to.
>Sure, the result is not predictable - we know that the entire table
>will be thrashed.
The result IS predictable. Wrong, perhaps (probably) - but predictable
and repeatable. If I know the data in the tables, I can predict the
result of this query - and you can add, remove or change indexes, add or
remove processors or memory or even use hints to force funny execution
plans, but none of these changes will change the execution plan.
The kind of mistake I was talking about is illustrated in the query
below:
ALTER TABLE titles
ADD sold_in char(4)
go
-- ANSI standard - will consistently throw an error
UPDATE titles
SET sold_in = (SELECT s.stor_id
FROM sales AS s
WHERE s.title_id = titles.title_id)
go
-- T-SQL proprietary - no error, no warning
UPDATE t
SET sold_in = s.stor_id
FROM titles AS t
JOIN sales AS s
ON s.title_id = t.title_id
go
SELECT title_id, sold_in
FROM titles
go
-- Same query, but force another execution plan
-- Now, the same query suddenly yields different results
UPDATE t
SET sold_in = s.stor_id
FROM titles AS t
INNER HASH JOIN sales AS s
ON s.title_id = t.title_id
go
SELECT title_id, sold_in
FROM titles
go
(snip)
>And this brings to another great advantage with the FROM syntax: you
>use the same idiom for UPDATE/DELETE as you do for SELECT. If you are
>uncertain whether your conditions are correct, you can easily transform
>your UPDATE to a SELECT.
>
>Again, I'm putting emphasis on the cognitive side of things.
You're right there - transforming a working SELECT to a working T-SQL
UPDATE is lots easier than creating the equivalent ANSI UPDATE. (But I
can tell you from experience that the latter also becomes a routine job
after the first 50 or so).
I personally believe that a good programmer should master both versions:
the ANSI version becuas he might be required to write portable code, or
might get himself in a situation where even SQL Server doesn't like the
T-SQL version, and the T-SQL syntax because he might need it to optimize
poor performing parts of the code.
>> 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?
>
>Any time I see someone touting the ANSI syntax for UPDATE and DELETE,
>I will reserve the right to bump in.
By all means, do. People *should* know both variants!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Navigation:
[Reply to this message]
|