November 8, 2005 at 11:33 am
I have a composite clustered index. The first column is an integer, the second column is a char(1). In the index definition, column_one is defined as DESC.
The select list of the query is completely covered by the clustered index. When I execute the query with no 'order by' clause the data is returned in DESC sequence which is correct. When I execute the query with an 'order by' clause specifying column_one ASC, the data is returned in DESC sequence which is incorrect. If I execute the query with an 'order by' clause specifying column_one DESC, the data is returned in ASC sequence which is incorrect.
Has anyone experienced this?
November 8, 2005 at 2:15 pm
Hello dwo,
Can you please post the actual DDL for the table and the index? Sample data and the actual select statements you are using would also be helpful.
Thanks
Wayne
November 8, 2005 at 2:34 pm
I guess there is confusion in What is ASC and DESC.
When there is no order by SQL Server would return the rows in ASC. When we specify DESC then it will return Descending order.
Check this
SET NOCOUNT ON
DECLARE @MyTable TABLE
(
myID INT PRIMARY KEY,
MyDesc VARCHAR(10)
)
INSERT @MyTable
SELECT 5, 'DESC5' UNION
SELECT 3, 'DESC3' UNION
SELECT 1, 'DESC1' UNION
SELECT 4, 'DESC4' UNION
SELECT 2, 'DESC2'
SELECT * FROM @MyTable
SELECT * FROM @MyTable ORDER BY myID
SELECT * FROM @MyTable ORDER BY myID DESC
Regards,
gova
November 8, 2005 at 6:15 pm
I don't understand the problem.
I tried this:
Create TABLE #MyTable (
myID INT,
MyDesc VARCHAR(10),
CONSTRAINT PK_MyTable_1 PRIMARY KEY CLUSTERED (Myid DESC)
)
INSERT #MyTable
SELECT 5, 'DESC5' UNION
SELECT 3, 'DESC3' UNION
SELECT 1, 'DESC1' UNION
SELECT 4, 'DESC4' UNION
SELECT 2, 'DESC2'
SELECT * FROM #MyTable
SELECT * FROM #MyTable ORDER BY myID
SELECT * FROM #MyTable ORDER BY myID DESC
DROP TABLE #MyTable
Works perfect, returns lines in right order everytime.
Check your query or data you've inserted.
_____________
Code for TallyGenerator
November 9, 2005 at 2:35 pm
I understand very well the difference between ASC and DESC in an order by clause. Please don't insult my intelligence. You missed the point of the post. Try this:
-----------------------------------------------------------
-- 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
-----------------------------------------------------------
-- Create Clustered Index
-----------------------------------------------------------
create clustered index cluster_001
on table_one
(column_one desc, column_two)
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
-----------------------------------------------------------
-- 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 -- Descending 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 DESC
-----------------------------------------------------------
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 desc
go
-----------------------------------------------------------
-- ORDER BY Clause on Column_One ASC
-----------------------------------------------------------
SELECT column_one, column_two -- Descending 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 asc
go
-----------------------------------------------------------
-- No AND Clause
-- No ORDER BY Clause
-----------------------------------------------------------
SELECT column_one, column_two -- Descending 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 -- 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 DESC
-----------------------------------------------------------
SELECT column_one, column_two -- Descending 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 desc
go
-----------------------------------------------------------
-- No AND Clause
-- ORDER BY Clause on Column_One ASC
-----------------------------------------------------------
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 asc
go
November 9, 2005 at 6:34 pm
That is extremely weird! And I don't think anyone was insulting your intelligence; you'd be suprised at what kind of questions get asked, and how are we to know how much someone knows when they don't provide a DDL. In this instance, this question was impossible to answer without the DDL.
The problem is your clustered index. You made it descending for some reason. Essentially this causes "order by desc desc", which becomes the same as "order by asc"
Make it a regular clustered index and you'll be fine.
create clustered index cluster_001
on table_one
(column_one, column_two)
go
Signature is NULL
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply