reordering in paralellism-multiprocessor machine

Excuse me if this is a double post!
Consider these queries:
1) select C,D,E,F from T where (A - B) >= 0
2) select C,D,E,F from T where B >= 0 and B <10
3) select * from T where (A - B) >= 0
4) select * from T where B >= 0 and B <10
Description of the table 'T':
256 columns
No identity column defined on T
columns Z,Y,X have nonclustered on PRIMARY
column C, being the primary key column , has the index: clustered, unique,
primary key located on PRIMARY
A is of type int and B is of type decimal.
No. of rows in the table: 100406
Process followed for replication: run these queries in a MULTI PROCESSOR
machine. See the execution plan.
Issue:
Queries 1, 3, 4 execute by the serial plan, whereas query 2 executes by
parallel plan. Still worse, the order in which the records are returned are
not the same each time query 2 is run. When tried with the hint option
(MAXDOP 1), the query 2 executes in serial mode, without re-arrangement. Need
an answer for the rearrangement of the rows resulting in case of query 2.
This has been discussed ad-nauseum. The order of returned rows is never
guaranteed unless the query includes an order by clause.
"Saravana" <Saravana@.discussions.microsoft.com> wrote in message
news:6B76B67A-AD98-415D-8296-9619039A7377@.microsoft.com...
> Excuse me if this is a double post!
> Consider these queries:
> 1) select C,D,E,F from T where (A - B) >= 0
> 2) select C,D,E,F from T where B >= 0 and B <10
> 3) select * from T where (A - B) >= 0
> 4) select * from T where B >= 0 and B <10
> Description of the table 'T':
> 256 columns
> No identity column defined on T
> columns Z,Y,X have nonclustered on PRIMARY
> column C, being the primary key column , has the index: clustered, unique,
> primary key located on PRIMARY
> A is of type int and B is of type decimal.
> No. of rows in the table: 100406
> Process followed for replication: run these queries in a MULTI PROCESSOR
> machine. See the execution plan.
> Issue:
> Queries 1, 3, 4 execute by the serial plan, whereas query 2 executes
by
> parallel plan. Still worse, the order in which the records are returned
are
> not the same each time query 2 is run. When tried with the hint option
> (MAXDOP 1), the query 2 executes in serial mode, without re-arrangement.
Need
> an answer for the rearrangement of the rows resulting in case of query 2.
>
|||Scott,
Isn't there any other way of stopping this reordering: tweaking of the
tables, or the columns..
In other words: what is the exact quantitative statistic that is considered
by the server for executing a query paralelly or serially.. i came to know
about execution costs, but i am not able to get any useful information from
the execution plans from the query anaylzer. What i am more concerned is that
query 2 is re-ordered whereas query 1 is ordered...
Thanks.
"Scott Morris" wrote:

> This has been discussed ad-nauseum. The order of returned rows is never
> guaranteed unless the query includes an order by clause.
>
|||As I said - no order by clause, no guarantee. There is a thread in
..progamming with the subject "order by and identity" that was active
yesterday. You might find the discussion interesting as it deals with the
same basic issue. You can search the newsgroups for discussions / rants on
this FAQ.
"Saravana" <Saravana@.discussions.microsoft.com> wrote in message
news:0FD75B28-2543-4128-8F6F-E888F5110751@.microsoft.com...
> Scott,
> Isn't there any other way of stopping this reordering: tweaking of the
> tables, or the columns..
> In other words: what is the exact quantitative statistic that is
considered
> by the server for executing a query paralelly or serially.. i came to know
> about execution costs, but i am not able to get any useful information
from
> the execution plans from the query anaylzer. What i am more concerned is
that
> query 2 is re-ordered whereas query 1 is ordered...
> Thanks.
> "Scott Morris" wrote:
>
|||Scott is 100% correct. If you want a specific order to be guaranteed for
the returned data you MUST specify an order by clause, period, end of story.
Andrew J. Kelly SQL MVP
"Saravana" <Saravana@.discussions.microsoft.com> wrote in message
news:0FD75B28-2543-4128-8F6F-E888F5110751@.microsoft.com...
> Scott,
> Isn't there any other way of stopping this reordering: tweaking of the
> tables, or the columns..
> In other words: what is the exact quantitative statistic that is
> considered
> by the server for executing a query paralelly or serially.. i came to know
> about execution costs, but i am not able to get any useful information
> from
> the execution plans from the query anaylzer. What i am more concerned is
> that
> query 2 is re-ordered whereas query 1 is ordered...
> Thanks.
> "Scott Morris" wrote:
>