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