Hi,

I'm a little bit confused about index and 'order by'.
1) when doing 'order by' in a select statement, the table is sorted, even if
no index is defined. How does sql server then sort the table?
2) does an index (non clustered) sort data or it's only used for retrieving
data fast in a select?
3) if a create several (non-clustered) indexes, how does sql server know
which one it must use?
4) a clustered index sorts the table; if i put 'order by' in the select,
according which field is the table then sorted?

Thanks
Chris

> 1) when doing 'order by' in a select statement, the table is sorted, even if no index is defined.
> How does sql server then sort the table?

Through a "sort" operation during execution of the query. You can see this in the execution plan.


> 2) does an index (non clustered) sort data or it's only used for retrieving data fast in a select?

The index is sorted according to the columns in the index, but not the data per se. The index
contains "pointers" to the data.

> 3) if a create several (non-clustered) indexes, how does sql server know which one it must use?

The optimizer make that decision by estimating cost for various different ways of executing a query
and then picks what it considers being the cheapest way.


> 4) a clustered index sorts the table; if i put 'order by' in the select, according which field is
> the table then sorted?

A SELECT statement doesn't sort the table. It returns a result which draws information *from* the
table. The table in quesion is still sorted accoring to the clustered index.

I suggest you check out the "Physical Database Architecture" section in Books Online.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Chris" <gddfd@er.df> wrote in message news:uhxoajS0HHA.3400@TK2MSFTNGP03.phx.gbl...
> Hi,
>
> I'm a little bit confused about index and 'order by'.
> 1) when doing 'order by' in a select statement, the table is sorted, even if no index is defined.
> How does sql server then sort the table?
> 2) does an index (non clustered) sort data or it's only used for retrieving data fast in a select?
> 3) if a create several (non-clustered) indexes, how does sql server know which one it must use?
> 4) a clustered index sorts the table; if i put 'order by' in the select, according which field is
> the table then sorted?
>
> Thanks
> Chris
>

Thanks for explanations ..

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> schreef
in bericht news:5F1B8C8A-6311-47A2-9A00-56CDAC327BD5@microsoft.com...
>> 1) when doing 'order by' in a select statement, the table is sorted, even
>> if no index is defined. How does sql server then sort the table?
>
> Through a "sort" operation during execution of the query. You can see this
> in the execution plan.
>
>
>> 2) does an index (non clustered) sort data or it's only used for
>> retrieving data fast in a select?
>
> The index is sorted according to the columns in the index, but not the
> data per se. The index contains "pointers" to the data.
>
>> 3) if a create several (non-clustered) indexes, how does sql server know
>> which one it must use?
>
> The optimizer make that decision by estimating cost for various different
> ways of executing a query and then picks what it considers being the
> cheapest way.
>
>
>> 4) a clustered index sorts the table; if i put 'order by' in the select,
>> according which field is the table then sorted?
>
> A SELECT statement doesn't sort the table. It returns a result which draws
> information *from* the table. The table in quesion is still sorted
> accoring to the clustered index.
>
> I suggest you check out the "Physical Database Architecture" section in
> Books Online.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Chris" <gddfd@er.df> wrote in message
> news:uhxoajS0HHA.3400@TK2MSFTNGP03.phx.gbl...
>> Hi,
>>
>> I'm a little bit confused about index and 'order by'.
>> 1) when doing 'order by' in a select statement, the table is sorted, even
>> if no index is defined. How does sql server then sort the table?
>> 2) does an index (non clustered) sort data or it's only used for
>> retrieving data fast in a select?
>> 3) if a create several (non-clustered) indexes, how does sql server know
>> which one it must use?
>> 4) a clustered index sorts the table; if i put 'order by' in the select,
>> according which field is the table then sorted?
>>
>> Thanks
>> Chris
>>
>

When you do "ORDER BY", the required index is created on the fly, the
resulting data is sent back to requesting app in the order dictated by this
temporary index, and then the temp index is deleted.

ThanQ...


"Chris" wrote:

> Hi,
>
> I'm a little bit confused about index and 'order by'.
> 1) when doing 'order by' in a select statement, the table is sorted, even if
> no index is defined. How does sql server then sort the table?
> 2) does an index (non clustered) sort data or it's only used for retrieving
> data fast in a select?
> 3) if a create several (non-clustered) indexes, how does sql server know
> which one it must use?
> 4) a clustered index sorts the table; if i put 'order by' in the select,
> according which field is the table then sorted?
>
> Thanks
> Chris
>
>
>

This is not true. SQL Server will sort the data when you use ORDER BY, if
there is not an existing useful index in place already.
There is no index built internally to do this.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com


"sme" <sme@discussions.microsoft.com> wrote in message
news:95CDA43B-E2E9-45D3-AA3F-49DDA2F71B02@microsoft.com...
> When you do "ORDER BY", the required index is created on the fly, the
> resulting data is sent back to requesting app in the order dictated by
> this
> temporary index, and then the temp index is deleted.
>
> ThanQ...
>
>
> "Chris" wrote:
>
>> Hi,
>>
>> I'm a little bit confused about index and 'order by'.
>> 1) when doing 'order by' in a select statement, the table is sorted, even
>> if
>> no index is defined. How does sql server then sort the table?
>> 2) does an index (non clustered) sort data or it's only used for
>> retrieving
>> data fast in a select?
>> 3) if a create several (non-clustered) indexes, how does sql server know
>> which one it must use?
>> 4) a clustered index sorts the table; if i put 'order by' in the select,
>> according which field is the table then sorted?
>>
>> Thanks
>> Chris
>>
>>
>>