Re: Order by in a INSERT INTO..SELECT
- From: "David Portas" <REMOVE_BEFORE_REPLYING_dportas@xxxxxxx>
- Date: 23 Apr 2006 01:10:36 -0700
drawnai@xxxxxxxxxxx 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
--
.
- Follow-Ups:
- Re: Order by in a INSERT INTO..SELECT
- From: drawnai
- Re: Order by in a INSERT INTO..SELECT
- From: drawnai
- Re: Order by in a INSERT INTO..SELECT
- From: Erland Sommarskog
- Re: Order by in a INSERT INTO..SELECT
- References:
- Re: Order by in a INSERT INTO..SELECT
- From: drawnai
- Re: Order by in a INSERT INTO..SELECT
- From: David Portas
- Re: Order by in a INSERT INTO..SELECT
- From: drawnai
- Re: Order by in a INSERT INTO..SELECT
- From: drawnai
- Re: Order by in a INSERT INTO..SELECT
- Prev by Date: SQL Server 2005 Import/Export not copying stored procedures
- Next by Date: Re: Order by in a INSERT INTO..SELECT
- Previous by thread: Re: Order by in a INSERT INTO..SELECT
- Next by thread: Re: Order by in a INSERT INTO..SELECT
- Index(es):
Relevant Pages
|
Loading
