Inconsistent sort order using ORDER BY clause

I am getting the resultset sorted differently if I use a column number in the ORDER BY clause instead of a column name.

Product: Microsoft SQL Server Express Edition
Version: 9.00.1399.06
Server Collation: SQL_Latin1_General_CP1_CI_AS

for example,

create table test_sort
( description varchar(75) );

insert into test_sort values('Non-A');
insert into test_sort values('Non-O');
insert into test_sort values('Noni');
insert into test_sort values('Nons');

then execute the following selects:
select
*
from
test_sort
order by
cast( 1 as nvarchar(75));

select
*
from
test_sort
order by
cast( description as nvarchar(75));

Resultset1
-
Non-A
Non-O
Noni
Nons

Resultset2
-
Non-A
Noni
Non-O
Nons

Any ideas?As far as i figured your query out, i am just wondering why this works for you as the 1 will be casted to a constant string which should not be allowed in the order by clause. Are you sure this works for you ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de|||First, you are running the release version of 2005. You should install at least SP1.

Second, if you install SP1, you would see an error:

Msg 408, Level 16, State 1, Line 9
A constant expression was encountered in the ORDER BY list, position 1.

Because you are sorting by the NUMBER 1, not column 1 by using the cast. So basically you have no sort.

In What Order Index Sorts the Data?

Hi,

I want to ask a basic question, that is

IN WHAT ORDER A CLUSTERED INDEX SORT THE DATA IN THE COLUMN?

Somewhere in the MSDN library I read the following line:

"A clustered index physically sorts the table's contents in the order of the specified index columns"

But Sorting means it will be in ASCENDING ORDER (ASC) or It will be in DESCENDING ORDER (DESC)

So my question is lets suppose a column on which the cluistered index is defined and it contains character data liek abcd so in wht order it will sort the data alphabetically ASC or DESC

or

If the same above case with integer type of values, if column having integer values then in wht order the data in the table will be sorted.

?

Thanks..!!!

You are the one that has to speciy it. By default SS will use ASC.

Code Snippet

use tempdb

go

create table dbo.t1(

c1 int identity not null,

constraint pk_t1 primary key clustered (c1 ASC))

go

drop table dbo.t1

go

create table dbo.t1(

c1 int identity not null,

constraint pk_t1 primary key clustered (c1 DESC))

go

drop table dbo.t1

go

create table dbo.t1(

c1 int identity not null

)

go

create unique clustered index t1_c1_u_c_ix

on dbo.t1(c1 ASC)

go

drop index t1_c1_u_c_ix on dbo.t1

go

create unique clustered index t1_c1_u_c_ix

on dbo.t1(c1 DESC)

go

drop table dbo.t1

go

AMB|||

Ascending by default, but you can specify descinding if you'd like

|||

Hi,

I want to ask a basic question, that is

IN WHAT ORDER A CLUSTERED INDEX SORT THE DATA IN THE COLUMN?

Somewhere in the MSDN library I read the following line:

"A clustered index physically sorts the table's contents in the order of the specified index columns"

But Sorting means it will be in ASCENDING ORDER (ASC) or It will be in DESCENDING ORDER (DESC)

So my question is lets suppose a column on which the cluistered index is defined and it contains character data liek abcd so in wht order it will sort the data alphabetically ASC or DESC

or

If the same above case with integer type of values, if column having integer values then in wht order the data in the table will be sorted.

?

Thanks..!!!

--
PRASHANT PANDEY

Aargh!

Prashant,

Unfortunately, this information is simply wrong. There is absolutely no way to do anything at all in SQL Server to guarantee the physical order of the data. This is a good thing. If the data could be kept this way, you might have to move many gigabytes of data just to insert one row in the middle of a huge table. This would be terrible. "Ok, everyone move over to make room for the new person."

A clustered index does optimize the data storage for retrieval in the order of the clustered key columns, but if you want to be certain that data from a SELECT query comes back in a particular order, you absolutely must include an ORDER BY clause for that SELECT.

You may discover that without the order by, it works for years, and for millions of repetitions, but nevertheless, the order is not guaranteed, and suddenly when your table gets large, or you move to a different storage or processor configuration, or you apply a service pack, you may find you no longer see data in the order you expect.

Just in case all you were asking is in what logical order the data is organized when a column is specified as a key column, it is ascending order unless you specify otherwise with DESC.

Steve Kass

Drew University

http://www.stevekass.com

IN SQL Server Management Studio - Can connect to database sort of but Icon isn't green anymore

Preface: I'm a newbie at 2005 and sql server in general.

I can connect to a sql server (2005 express) but I can only make views and not run queries. When I open up a new query and create it with query designer and click add table I see no tables, views or anything in the box. When I create a new view in the database I do see tables and views though.

The little circle part of the icon on the server in the left hand pane is clear now when before it was green. I still can connect to the database ok but I can't use the query analyzer for queries anymore. I need to do that because I do have some slow running queries.

~Capt howdy

Select the database first (ie:AdventureWorks), 'new query' and then click design view.

|||I'm such a n00b with sql server it hurts sometimes. Thanks! I am used to access but it is definitely time to step up.

~Capt. Howdy

IN SQL Server Management Studio - Can connect to database sort of but Icon isn't green anym

Preface: I'm a newbie at 2005 and sql server in general.

I can connect to a sql server (2005 express) but I can only make views and not run queries. When I open up a new query and create it with query designer and click add table I see no tables, views or anything in the box. When I create a new view in the database I do see tables and views though.

The little circle part of the icon on the server in the left hand pane is clear now when before it was green. I still can connect to the database ok but I can't use the query analyzer for queries anymore. I need to do that because I do have some slow running queries.

~Capt howdy

Select the database first (ie:AdventureWorks), 'new query' and then click design view.

|||I'm such a n00b with sql server it hurts sometimes. Thanks! I am used to access but it is definitely time to step up.

~Capt. Howdy