Order by in a INSERT INTO..SELECT

#21

Erland Sommarskog wrote:

Quote:

(drawnai (AT) hotmail (DOT) com) writes:
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.

But the ability isn't there. It only looks like it is. That's why it's
undefined.

In SQL Server 6.5 GROUP BY implied an ORDER BY. Not so any more. In SQL 2000
people would put TOP 100 PERCENT ORDER BY in a view, and they found that
they did seem to need an ORDER BY when selecting from the view. In SQL 2005
more than one have found that they don't get away with it.

You get the result you get by happenstance. There is nothing that Microsoft
can remove, because they never added it. One day the optimizer decides to
do a different plan, and you don't get the result you wanted.

Of course, you may be prepared to take the gamble, but the day it breaks,
it's going to break hard.

Everything you say is true. I don't disagree with any of it. However,
from experience,
an ordered update against a single table is worth the risk, that I
might have to (one day) rewrite it. I'm not writing space targeted
software (anymore.)

I find this particularly the case, because I've never seen an explicit
index hint against
a single table, to be ignored. Similarly, microsoft actually advertise
the update set
@fred = col = @fred + 1.

I feel justified in my assertion that "by the time they don't support
it, they'll have introduced something faster," because on the whole,
(and very definitely in this case)
they have done.

Most tech authorities hold that 2 second is an acceptable response time
for a webpage. I maintain that anything over 40 mS is failure.

As for "It may not work at sometime in the future." Well that's the
most ridiculous strawman I've heard in a long time.

I judge that keeping an eye on code is an ongoing requirement, and I
constantly watch for changes that invalidate something I depend on.

Quote:

From .net 1.1, to .net 2.0 invoking the Sleep method, meant a change
from a class member, to a static member, meaning only a thread can

sleep itself. I don't see my watching for changes to unlisted features
to be any different to changes to listed features.

Quote:

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#22

David Portas wrote:

Quote:

drawnai (AT) hotmail (DOT) com wrote:
David Portas wrote:
drawnai (AT) hotmail (DOT) 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

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.

Are you saying my query doesn't work? No? I didn't think so.

Quote:

Since you can't predict whether those situations will arise at runtime
you have to take a calculated risk before you implement those

Not true. I actually run them to find out.

Quote:

undocumented tricks. Microsoft's history of breaking changes to

Not so far. Nothing I've written in the last ten years has been broken
by a change anywhere near so much as those broken by changes to
advertised features.

Quote:

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.

We agree then. Like I said, it's a pure cost benefit analysis. You
contend that
this approach has risks, but imply that I'm not aware of that. This is
simply
not true. I'm well aware of the risks, which is why I have a list of
features I
use that are periodically checked. This includes documented features.

In fact in my experience, documented features change more than
undocumented
features.

Quote:

--
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/m...S,SQL.90).aspx
--

#23

drawnai (AT) hotmail (DOT) com wrote:

Quote:

Are you saying my query doesn't work? No? I didn't think so.

I'm saying you can't demonstrate that it does.

Quote:

Since you can't predict whether those situations will arise at runtime
you have to take a calculated risk before you implement those

Not true. I actually run them to find out.

Which proves nothing because execution plans can change at runtime.

Quote:

I'm well aware of the risks, which is why I have a list of
features I
use that are periodically checked. This includes documented features.

Proves nothing. See above.

--
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/m...S,SQL.90).aspx
--

#24

David Portas wrote:

Quote:

drawnai (AT) hotmail (DOT) com wrote:

Are you saying my query doesn't work? No? I didn't think so.

I'm saying you can't demonstrate that it does.

Since you can't predict whether those situations will arise at runtime
you have to take a calculated risk before you implement those

Not true. I actually run them to find out.

Which proves nothing because execution plans can change at runtime.

I'm well aware of the risks, which is why I have a list of
features I
use that are periodically checked. This includes documented features.

Proves nothing. See above.

Neither does it if it's written down.

Quote:

--
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/m...S,SQL.90).aspx
--

#25

what happens if the table is big and this UPDATE is executed by several
processors in parallel?

#26

I'm well aware of Codd, thank you very much. I'm also aware that SQL is
a relational language.

Ordering is however, a requirement for the real world, and I take
advantage of all the features of an application that are published,
and also the features that I deem are safe, that aren't published.

It's interesting you say that I could be replaced, because in 14 years
as a contractor, I've never (that's Never) failed to deliver working
project, as advertised. I've until very recently, deliberately gone
into each project as junior developer, taken over, and then fixed it,
and am usually the last contractor standing. I have prototypes I've
written, that became airborne software, and are flying, in space, as
well as in the air, and I replaced the whole of site server and 150
pages of ASP, with a single stored procedure for a very busy (and
successful) website.
This single stored procedure contained ALL the business logic. It was
done, because it needed to work, and the company couldn't get the
people in time to write it. This ran for two years before I was
replaced (correctly I believe) with a net datatable multi-tier based
system, once there was sufficient resource to continue with
development.

Books are useful to be sure, and I do have an extensive library on
software engineering, but a book is only as good as its authors are
clever. I'm happy to stand by my record, of having never failed to
deliver.

#27

When the day comes that Microsoft runs parallel query against a
sequential update, then it will be spotted in UAT (if I haven't already
seen it,) and replaced with a cursor or some other cunning tactic.

I don't imagine I'll ever see that however, because I don't suppose
that Microsoft will rollback the row_number() (Over col1, col2, col3)
function in SQL 2010, just to allow it to introduce its new "parallel
sequential (tm)" technology - a process that allocates an unknown in
advance amount sequential numbers of in parallel.

Out of interest, how does one allocate an index in sequence in parallel
against a table whose size is unknown at the start of the update
without wasting huge resources calculating the tree sizes? Does one
mark the entire index as "untouched", then excute massive parallel jobs
against parts of it, ensuring never to touch the same record twice?
Perhaps, it asks god how big each table leaf root is, and then assigns
ranges of numbers of exact size, (taking into account any inserts or
deletes that are going to happen, after it starts and before it ends)
to ensure that when it distributes the job to it's SMP array, that each
row gets a number that is exactly unique, and contiguous. Perhaps you
could start at one end of the table, with the length of it (including
any intermediate insert/deletes as extrapolated from a small piece of
fairy cake), and start at the other end of the same, and then work
inwards.

My solution would be to simply use magic. This is guaranteed to work.

#28

drawnai (AT) hotmail (DOT) com wrote:

Quote:

When the day comes that Microsoft runs parallel query against a
sequential update, then it will be spotted in UAT (if I haven't already
seen it,) and replaced with a cursor or some other cunning tactic.

I don't imagine I'll ever see that however, because I don't suppose
that Microsoft will rollback the row_number() (Over col1, col2, col3)
function in SQL 2010, just to allow it to introduce its new "parallel
sequential (tm)" technology - a process that allocates an unknown in
advance amount sequential numbers of in parallel.

Out of interest, how does one allocate an index in sequence in parallel
against a table whose size is unknown at the start of the update
without wasting huge resources calculating the tree sizes? Does one
mark the entire index as "untouched", then excute massive parallel jobs
against parts of it, ensuring never to touch the same record twice?
Perhaps, it asks god how big each table leaf root is, and then assigns
ranges of numbers of exact size, (taking into account any inserts or
deletes that are going to happen, after it starts and before it ends)
to ensure that when it distributes the job to it's SMP array, that each
row gets a number that is exactly unique, and contiguous. Perhaps you
could start at one end of the table, with the length of it (including
any intermediate insert/deletes as extrapolated from a small piece of
fairy cake), and start at the other end of the same, and then work
inwards.

My solution would be to simply use magic. This is guaranteed to work.

Even sequential scans don't always start at the same place in the index
due to enhanced read-ahead (AKA merry-go-round scan). AFAIK that
doesn't apply to index updates today but it could perhaps do so in
future. That's a good example of something you may be unlikely to spot
in a unit test (unless you simulate load). It will likely show up if
you test to peak production workload but do you always regression test
on that scale for every service pack? Engine changes have gone in SPs
before.

--
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/m...S,SQL.90).aspx
--

#29

David Portas wrote:

Quote:

drawnai (AT) hotmail (DOT) com wrote:
When the day comes that Microsoft runs parallel query against a
sequential update, then it will be spotted in UAT (if I haven't already
seen it,) and replaced with a cursor or some other cunning tactic.

I don't imagine I'll ever see that however, because I don't suppose
that Microsoft will rollback the row_number() (Over col1, col2, col3)
function in SQL 2010, just to allow it to introduce its new "parallel
sequential (tm)" technology - a process that allocates an unknown in
advance amount sequential numbers of in parallel.

Out of interest, how does one allocate an index in sequence in parallel
against a table whose size is unknown at the start of the update
without wasting huge resources calculating the tree sizes? Does one
mark the entire index as "untouched", then excute massive parallel jobs
against parts of it, ensuring never to touch the same record twice?
Perhaps, it asks god how big each table leaf root is, and then assigns
ranges of numbers of exact size, (taking into account any inserts or
deletes that are going to happen, after it starts and before it ends)
to ensure that when it distributes the job to it's SMP array, that each
row gets a number that is exactly unique, and contiguous. Perhaps you
could start at one end of the table, with the length of it (including
any intermediate insert/deletes as extrapolated from a small piece of
fairy cake), and start at the other end of the same, and then work
inwards.

My solution would be to simply use magic. This is guaranteed to work.

Even sequential scans don't always start at the same place in the index
due to enhanced read-ahead (AKA merry-go-round scan). AFAIK that
doesn't apply to index updates today but it could perhaps do so in
future. That's a good example of something you may be unlikely to spot
in a unit test (unless you simulate load). It will likely show up if
you test to peak production workload but do you always regression test
on that scale for every service pack? Engine changes have gone in SPs
before.

And will no doubt go further in future, due to the new CTP approach to
delivery.

Quote:

--
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/m...S,SQL.90).aspx
--

#30

--CELKO-- wrote:

Quote:

Yes; shot you and replace you with a programmer who has read the first
10 pages of **any** RDBMS book. You are posting realllllllly baaaaad
questions. Please take some time to catch up over the weekend before
you ask us to do your job for you again.

SQL is a set-orient language. Tables -- by definition -- have no
ordering. That is the nature of a set. Have you ever read Dr. Codd's
12 rules for RDBMS/ Look up the Information Principle: all
relationships are shown as values in columns. Ordering is a
relationship, so you need a column(s) for it.

If you do not know who Dr. Codd is or his rules, then you are like a
Geometry student who never heard of Euclid.

When faced with crass pointless comments like this, it's unsurprising
that
software engineering doesn't advance as fast as it could. Relational
theory is
no cleverer than Codd, (and maybe Date) was. They weren't gods. They
put forward
software engineering maybe only twice as far as Straustrup put it back
with
the abomination of c++.

I'm happy to decline to Bruce Lee. No matter how good a technique, be
it a
punch or a throw or a kick, (or relational theory, or xml, or
hibernate, or a
standards document) it becomes a weakness when one becomes obsessed
with it.