Order By not working as thought

I have a table that has a two column clustered index. Col 1 is int and
col 2 is char, defined as desc.
When I run a query selecting only those two columns and use an Order By
Col1 ASC, the result set returned has the Col 1 returned in DESC. And
when I use an Order By Col 1 DESC, the result set returned has the Col1
returned in ASC.
The plan shows only a clustered index seek. So even though my
clustered index is defined as desc, why would that affect the way the
Order By clause chooses to return the results? ASC in the Order By
should still return the rows from 1 to 100 not from 100 to 1 even
though the clustered index is desc.
Am I wrong in my thinking...'
Thanks!!Are you saying that SQL Server reverses the meaning of ASN and DESC on your ORDER BY when have an
index defined as DESC instead of ASC. If so, it is a bug and should be reported to MS (and see first
if such a bug has been reported, test recent service pack etc). Post a repro script if you want us
to rest or recent build or 2005.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mghale" <martinghale@.yahoo.com> wrote in message
news:1131465273.531584.225150@.o13g2000cwo.googlegroups.com...
>I have a table that has a two column clustered index. Col 1 is int and
> col 2 is char, defined as desc.
> When I run a query selecting only those two columns and use an Order By
> Col1 ASC, the result set returned has the Col 1 returned in DESC. And
> when I use an Order By Col 1 DESC, the result set returned has the Col1
> returned in ASC.
> The plan shows only a clustered index seek. So even though my
> clustered index is defined as desc, why would that affect the way the
> Order By clause chooses to return the results? ASC in the Order By
> should still return the rows from 1 to 100 not from 100 to 1 even
> though the clustered index is desc.
> Am I wrong in my thinking...'
> Thanks!!
>|||That's exactly what I found. Actually it's a client of mine. They
came to me asking if this is the way it should be returning the
results. The funny thing is that if you add a column to the select
that is not included in the clustered index definition, the execution
plan shows a SORT and the result set is returned in the correct oder as
specified by the Order By clause.
When the select list only contained the two columns that make up the
clustered index, the execuation plan showed NO sort being performed and
returned the results backwards as specified by the Order By clause.
Thanks for the reply. I will advise my client.
Martin|||I you sure this query isn't a view? Views are supposed to be unordered
and an ORDER BY in a view will not exhibit the same (unsupported)
behaviour in 2005 as in 2000.
--
David Portas
SQL Server MVP
--|||Good point, David!
Martin, if you can produce a repro script, we are happy to check it out for you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1131476712.154075.133400@.g43g2000cwa.googlegroups.com...
>I you sure this query isn't a view? Views are supposed to be unordered
> and an ORDER BY in a view will not exhibit the same (unsupported)
> behaviour in 2005 as in 2000.
> --
> David Portas
> SQL Server MVP
> --
>|||David,
SQL 2000 support ORDER BY in query view using the TOP statement:
SELECT TOP 100 percent FROM table
ORDER BY column ASC | DESC
--
** * Esta msg foi útil pra você ? Então marque-a como tal. ***
Regards,
Rodrigo Fernandes
"David Portas" wrote:
> I you sure this query isn't a view? Views are supposed to be unordered
> and an ORDER BY in a view will not exhibit the same (unsupported)
> behaviour in 2005 as in 2000.
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks guys.
This isn't a view but a base table.
I'll see if I can get the client to allow me to post the DDL for the
table.
Essentially it is a table with x columns. Column one and two are Age
(INT) and Suffix(Char). The rest of the columns vary in type. The
table has a DESC clusterd index on Age, Suffix.
The select statement that produces the 'strange' results selects Age,
Suffice from tablename where Age in value, value, value, and Suffix not
in value, value Order By Age ASC
Then they execute this statement the ordering of Age (expected to be
from lowest to highest) is actually reversed and is listed from highest
to lowest. When you change the ASC to DESC in the Order By clause you
get just the opposite results. The order returned is the opposite to
what you expect to be returned according to the Oder By clause sort
order.
Also when we add a column to the select list that is not part of the
clustered index key and also add the column to the Order By clause
(i.e. Order By Age, ColNotInIndex) the execution plan shows a SORT
phase and the results are returned correctly as specified by the Order
By clause, either DESC or ASC.
Very strange and not what I expected although I have been researching
the forums and it looks like others have run into similar issues when
using DESC Clusterd Indexes...|||Yes, but that doesn't guarantee that you get the data back in that order when you query the view
(unless that query has a similar ORDER BY, of course). Using TOP 100 PERCENT in a view definition
has always been considered a hack, and in 2005 we will typically not see this have any impact of the
ordering of the rows returned from the view.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rodrigo Fernandes" <RodrigoFernandes@.discussions.microsoft.com> wrote in message
news:B8B5EFE3-848F-4CE2-BA9E-5C89F2868AFB@.microsoft.com...
> David,
> SQL 2000 support ORDER BY in query view using the TOP statement:
> SELECT TOP 100 percent FROM table
> ORDER BY column ASC | DESC
> --
> ** * Esta msg foi útil pra você ? Então marque-a como tal. ***
> Regards,
> Rodrigo Fernandes
>
> "David Portas" wrote:
>> I you sure this query isn't a view? Views are supposed to be unordered
>> and an ORDER BY in a view will not exhibit the same (unsupported)
>> behaviour in 2005 as in 2000.
>> --
>> David Portas
>> SQL Server MVP
>> --
>>|||I agree that using Top 100 percent is a hack but it also commonly used
so far as I have seen at my clients.
But still in my case were are hitting a base table and not a view.
Just a single base table. The fact is that using the Order By clause
should cause a SORT to be performed prior to the result set being
returned to ensure the data is in fact in the order specified by the
Order By clause. For whatever reason, on this table with a DESC
Clustered Index, the SORT is not being performed if the only columns in
the Order By Clause are key columns in the DESC Clustered Index. I
know SQL Server is not just ignoring the Order By clause because
depending on the sort order in the clause, either ASC or DESC it is
returning the rows in the exact opposite order. It's like it is using
the sorted (clustered) data and thinking that it is already ordered
correctly then applying the ASC or DESC directly to the order of the
clustered index which in this case in DESC. Perhaps that is why it
produces backwards results when using the Order By clause with ASC. It
doesn't sort but simply leaves the oder as it is in the ordered
(clustered) index which returns the results in a DESC order. Then when
you specify DESC in the order by clause SQL Server just reverses the
order of teh clusterd index which returns the rows in an ASC order.
Definitely not the right behavior for the Order By clause but it is my
best guess as to why this is happening. This is not a complicated
table structure and a very simple SQL Statement. Not alot of room for
human error on this one...|||SQL Server doesn't need a SORT as it can use the index to traverse the rows in the correct (!)
order. Look at the execution plan for the index usage and you will see ORDERED FORWARD or BACKWARD.
This is letting the execution engine that it must follow the index linked list to retrieve the rows,
not tie IAM page. But in this case, SQL Server obviously does it wrong. Again, with a repro we can
try it and verify etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mghale" <martinghale@.yahoo.com> wrote in message
news:1131481374.194917.233520@.z14g2000cwz.googlegroups.com...
>I agree that using Top 100 percent is a hack but it also commonly used
> so far as I have seen at my clients.
> But still in my case were are hitting a base table and not a view.
> Just a single base table. The fact is that using the Order By clause
> should cause a SORT to be performed prior to the result set being
> returned to ensure the data is in fact in the order specified by the
> Order By clause. For whatever reason, on this table with a DESC
> Clustered Index, the SORT is not being performed if the only columns in
> the Order By Clause are key columns in the DESC Clustered Index. I
> know SQL Server is not just ignoring the Order By clause because
> depending on the sort order in the clause, either ASC or DESC it is
> returning the rows in the exact opposite order. It's like it is using
> the sorted (clustered) data and thinking that it is already ordered
> correctly then applying the ASC or DESC directly to the order of the
> clustered index which in this case in DESC. Perhaps that is why it
> produces backwards results when using the Order By clause with ASC. It
> doesn't sort but simply leaves the oder as it is in the ordered
> (clustered) index which returns the results in a DESC order. Then when
> you specify DESC in the order by clause SQL Server just reverses the
> order of teh clusterd index which returns the rows in an ASC order.
> Definitely not the right behavior for the Order By clause but it is my
> best guess as to why this is happening. This is not a complicated
> table structure and a very simple SQL Statement. Not alot of room for
> human error on this one...
>|||Thanks for the clarification on the SORT. I will check the execution
plan. I guess I assumed that any time you include an Order By clause
in your query, the DBMS would perform a SORT as the final step to order
the rows according to the Order By clause.
I'll work with the client today to get the DDL for the table and
indexes, the exact SQL statement, the the levels of the OS and SQL
Server.
I won't be able to provide a sampling of data as my client is an
insurance company and restricted by HIPPA.
Thanks for all the feedback.
Martin|||OK,
I didn't get the client to provide their actual table DDL but we have
created a test script that reproduces the same outcome on three
different servers and found something very interesting. When using
just the first column of the clustered index which is an INT in the
WHERE clause, the Oder By clause is applied correctly. When we also
include the second column of the clustered index which is a char in the
WHERE clause, the Order By clause is applied incorrectly, the reverse
of what it should ordered according to the Order By clause. Here's the
info to reproduce the issue...
1. First create table and populate with data and do not add the
clusterd index yet...
---
-- Create Table
---
create table table_one
(
column_one int not null ,
column_two char(1) collate sql_latin1_general_cp1_ci_as not null
)
go
---
-- Insert Test Data
---
insert table_one values (1,'a')
insert table_one values (2,'b')
insert table_one values (3,'c')
insert table_one values (3,'k')
insert table_one values (3,'l')
insert table_one values (3,'x')
insert table_one values (4,'d')
insert table_one values (5,'e')
insert table_one values (6,'f')
insert table_one values (6,'m')
insert table_one values (6,'n')
insert table_one values (6,'x')
insert table_one values (7,'g')
insert table_one values (7,'s')
insert table_one values (8,'h')
insert table_one values (8,'q')
insert table_one values (9,'i')
insert table_one values (9,'o')
insert table_one values (9,'p')
insert table_one values (9,'x')
insert table_one values (10,'j')
insert table_one values (10,'p')
go
2. Run the following SELECT statements and make not of the correct
oder returned by each query. The comments to the side specify what
order the result set is returned and whether a SORT operation was part
of the execution plan. All these statements return the data and
perform a SORT as expected as no clustered index exists yet.
---
-- No ORDER BY Clause
---
SELECT column_one, column_two -- Ascending order returned
FROM table_one -- No SORT in Execution Plan
WHERE (column_one IN ('3','6','9'))
and (column_two not IN ('x'))
-- order by column_one
go
---
-- ORDER BY Clause on Column_One
---
SELECT column_one, column_two -- Ascending order returned
FROM table_one -- SORT in Execution Plan
WHERE (column_one IN ('3','6','9'))
and (column_two not IN ('x'))
order by column_one
go
---
-- ORDER BY Clause on Column_One DESC
---
SELECT column_one, column_two -- Descending order returned
FROM table_one -- SORT in Execution Plan
WHERE (column_one IN ('3','6','9'))
and (column_two not IN ('x'))
order by column_one desc
go
---
-- ORDER BY Clause on Column_One ASC
---
SELECT column_one, column_two -- Ascending order returned
FROM table_one -- SORT in Execution Plan
WHERE (column_one IN ('3','6','9'))
and (column_two not IN ('x'))
order by column_one asc
go
---
-- No AND Clause
-- No ORDER BY Clause
---
SELECT column_one, column_two -- Ascending order returned
FROM table_one -- No SORT in Execution Plan
WHERE (column_one IN ('3','6','9'))
-- and (column_two not IN ('x'))
-- order by column_one
go
---
-- No AND Clause
-- ORDER BY Clause on Column_One
---
SELECT column_one, column_two -- Ascending order returned
FROM table_one -- SORT in Execution Plan
WHERE (column_one IN ('3','6','9'))
-- and (column_two not IN ('x'))
order by column_one
go
---
-- No AND Clause
-- ORDER BY Clause on Column_One DESC
---
SELECT column_one, column_two -- Descending order returned
FROM table_one -- SORT in Execution Plan
WHERE (column_one IN ('3','6','9'))
-- and (column_two not IN ('x'))
order by column_one desc
go
---
-- No AND Clause
-- ORDER BY Clause on Column_One ASC
---
SELECT column_one, column_two -- Ascending order returned
FROM table_one -- SORT in Execution Plan
WHERE (column_one IN ('3','6','9'))
-- and (column_two not IN ('x'))
order by column_one asc
go
3. Create the Clustered Index with the following...
---
-- Create Clustered Index
---
create clustered index cluster_001
on table_one
(column_one desc, column_two)
go
4. Re-execute the above SELECT statements. Before sure to compare the
order of the result set returned with the order specified in the
comment. You will see on a few of the statements,l namely the SELECTS
that include both columns in the WHERE clause, the oder is returned in
the reverse as what it should be returned...
We have verified this behavior on three different servers and a
workstation. We are on Windows 2003 Server and SQL Server 2000 SP3.
We have not seen any mention of a fix for this in SP4 of SQL 2005
although we are going to execute this test on both of those
environments today as well as open an issue with Microsoft Support.
Have a look, see for your self. Very interesting results...
Thanks for all the replies!
Martin|||Definitely looks like a bug to me.
After creating the clustered index many of the resultsets are in an
incorrect order. I have run the script on SQL2K SP4 (8.00.2039).
The weird part is, that it even goes wrong if the first column is not
indexed as descending, as long as the second column is indexed as
descending. IOW, it also goes wrong with the index definition:
create clustered index cluster_001
on table_one
(column_one, column_two desc)
BTW: a work around is to change the column name in the ORDER BY clause
to a non trivial expression, for example ORDER BY RTRIM(column_one)
Gert-Jan
mghale wrote:
> OK,
> I didn't get the client to provide their actual table DDL but we have
> created a test script that reproduces the same outcome on three
> different servers and found something very interesting. When using
> just the first column of the clustered index which is an INT in the
> WHERE clause, the Oder By clause is applied correctly. When we also
> include the second column of the clustered index which is a char in the
> WHERE clause, the Order By clause is applied incorrectly, the reverse
> of what it should ordered according to the Order By clause. Here's the
> info to reproduce the issue...
> 1. First create table and populate with data and do not add the
> clusterd index yet...
> ---
> -- Create Table
> ---
> create table table_one
> (
> column_one int not null ,
> column_two char(1) collate sql_latin1_general_cp1_ci_as not null
> )
> go
> ---
> -- Insert Test Data
> ---
> insert table_one values (1,'a')
> insert table_one values (2,'b')
> insert table_one values (3,'c')
> insert table_one values (3,'k')
> insert table_one values (3,'l')
> insert table_one values (3,'x')
> insert table_one values (4,'d')
> insert table_one values (5,'e')
> insert table_one values (6,'f')
> insert table_one values (6,'m')
> insert table_one values (6,'n')
> insert table_one values (6,'x')
> insert table_one values (7,'g')
> insert table_one values (7,'s')
> insert table_one values (8,'h')
> insert table_one values (8,'q')
> insert table_one values (9,'i')
> insert table_one values (9,'o')
> insert table_one values (9,'p')
> insert table_one values (9,'x')
> insert table_one values (10,'j')
> insert table_one values (10,'p')
> go
> 2. Run the following SELECT statements and make not of the correct
> oder returned by each query. The comments to the side specify what
> order the result set is returned and whether a SORT operation was part
> of the execution plan. All these statements return the data and
> perform a SORT as expected as no clustered index exists yet.
> ---
> -- No ORDER BY Clause
> ---
> SELECT column_one, column_two -- Ascending order returned
> FROM table_one -- No SORT in Execution Plan
> WHERE (column_one IN ('3','6','9'))
> and (column_two not IN ('x'))
> -- order by column_one
> go
> ---
> -- ORDER BY Clause on Column_One
> ---
> SELECT column_one, column_two -- Ascending order returned
> FROM table_one -- SORT in Execution Plan
> WHERE (column_one IN ('3','6','9'))
> and (column_two not IN ('x'))
> order by column_one
> go
> ---
> -- ORDER BY Clause on Column_One DESC
> ---
> SELECT column_one, column_two -- Descending order returned
> FROM table_one -- SORT in Execution Plan
> WHERE (column_one IN ('3','6','9'))
> and (column_two not IN ('x'))
> order by column_one desc
> go
> ---
> -- ORDER BY Clause on Column_One ASC
> ---
> SELECT column_one, column_two -- Ascending order returned
> FROM table_one -- SORT in Execution Plan
> WHERE (column_one IN ('3','6','9'))
> and (column_two not IN ('x'))
> order by column_one asc
> go
> ---
> -- No AND Clause
> -- No ORDER BY Clause
> ---
> SELECT column_one, column_two -- Ascending order returned
> FROM table_one -- No SORT in Execution Plan
> WHERE (column_one IN ('3','6','9'))
> -- and (column_two not IN ('x'))
> -- order by column_one
> go
> ---
> -- No AND Clause
> -- ORDER BY Clause on Column_One
> ---
> SELECT column_one, column_two -- Ascending order returned
> FROM table_one -- SORT in Execution Plan
> WHERE (column_one IN ('3','6','9'))
> -- and (column_two not IN ('x'))
> order by column_one
> go
> ---
> -- No AND Clause
> -- ORDER BY Clause on Column_One DESC
> ---
> SELECT column_one, column_two -- Descending order returned
> FROM table_one -- SORT in Execution Plan
> WHERE (column_one IN ('3','6','9'))
> -- and (column_two not IN ('x'))
> order by column_one desc
> go
> ---
> -- No AND Clause
> -- ORDER BY Clause on Column_One ASC
> ---
> SELECT column_one, column_two -- Ascending order returned
> FROM table_one -- SORT in Execution Plan
> WHERE (column_one IN ('3','6','9'))
> -- and (column_two not IN ('x'))
> order by column_one asc
> go
> 3. Create the Clustered Index with the following...
> ---
> -- Create Clustered Index
> ---
> create clustered index cluster_001
> on table_one
> (column_one desc, column_two)
> go
> 4. Re-execute the above SELECT statements. Before sure to compare the
> order of the result set returned with the order specified in the
> comment. You will see on a few of the statements,l namely the SELECTS
> that include both columns in the WHERE clause, the oder is returned in
> the reverse as what it should be returned...
> We have verified this behavior on three different servers and a
> workstation. We are on Windows 2003 Server and SQL Server 2000 SP3.
> We have not seen any mention of a fix for this in SP4 of SQL 2005
> although we are going to execute this test on both of those
> environments today as well as open an issue with Microsoft Support.
> Have a look, see for your self. Very interesting results...
> Thanks for all the replies!
> Martin|||Thanks for the replies everyone. Gert-Jan - thanks for the work-around
suggestion.
My client is opening an issue with MS and we are also going to test for
the same behavior on SQL 2005...
Martinsql