Re: Order by in a INSERT INTO..SELECT
- From: "David Portas" <REMOVE_BEFORE_REPLYING_dportas@xxxxxxx>
- Date: 24 Apr 2006 02:37:45 -0700
drawnai@xxxxxxxxxxx wrote:
David Portas wrote:
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
Defined behaviour is as defined behaviour does. Outperforming an
equivalent
query ten to one is worth a rewrite 5 years from now, in the unlikely
event
that microsoft, remove the ability. (This goes against all precedents
as MS
have done very little but improve ability rather than remove it.)
I understand your purist position though, I used to be a software
engineering
purist, but after 30 years of writing code, I now hold the cost benefit
analysis
position. If I can generate, orders, multidimensional rolling averages,
and all
kinds of crap with a single pass of a table, rather than generating a
gig of
transaction log, and 5 Gig of tempdb allocation, then I do it.
Similarly, if I can implement the kind of parametric query, like
dabs.com's have
done, and multiorder search facility, at basically no cost, then I do
it.
By the time MS remove this facility, they'll replace it with something
better,
so there's no worries.
I don't consider myself a purist. In the spirit of Martin Fowler I
guess my ethic is something like "Any fool can write something that
works. Good developers write stuff that is verifiable and supportable."
That's not dogmatic. It's entirely practical because it reduces TCO for
the customer.
In this case the problem is not just that it may break in some distant
future. It is broken now. That is, even today there are situations
where multiple row variable assignments in queries just do not happen.
Since you can't predict whether those situations will arise at runtime
you have to take a calculated risk before you implement those
undocumented tricks. Microsoft's history of breaking changes to
undocumented behaviour is against you. I can think of multiple
precedents where undefined features have changed or failed in SQL
Server service packs, hotfixes and versions. The customer then has to
pay the price for development before he can patch his server.
--
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
- 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
- From: David Portas
- Re: Order by in a INSERT INTO..SELECT
- From: drawnai
- Re: Order by in a INSERT INTO..SELECT
- Prev by Date: Re: Order by in a INSERT INTO..SELECT
- 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
