|
|
Posted by David Portas on 04/23/06 11:10
drawnai@hotmail.com wrote:
> >
> > As for your comments about updates that affect multiple rows being
> > undefined, I'm afraid you're wrong.
> >
> > Update table set @fred = column = @fred + 1 is defined in SQL server's
> > own help, please look it up if you don't believe me.
> >
There is a difference between valid syntax and defined behaviour. BOL
does indeed say that your syntax is valid but nowhere does it define
what the result is supposed to be. The closest the documentation gets
is where it describes the equivalent multiple row assignment in a
SELECT statement. It says:
"SELECT @local_variable is typically used to return a single value into
the variable. However, when expression is the name of a column, it can
return multiple values. If the SELECT statement returns more than one
value, the variable is assigned the last value that is returned."
Note: "last value returned". That means the assignment only has to
happen once. The expression isn't necessarily evaluated for each row.
You cannot rely on the expression being evaluated for every row because
it doesn't always work.
In your example you try to force a particular execution plan onto your
code using an INDEX hint. But there is absolutely no reason why SQL
Server should always be required to implement a hint. Indexes are
intended as an optimization tool - they are not supposed to affect
logical behaviour of code.
So all I'm saying is that you should be very cautious with this UPDATE.
In the past we have seen too many undocumented features and smart
little tricks that fail or change in each new version. Unfortunately,
SQL Server is still full of "features" that give undefined results. If
you are doing a one-off update that doesn't matter much - you can
easily verify the results afterwards - but if you put this sort of
thing into production code you run the risk of it breaking under a
future version or service pack.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|