Does the order index itself?

Question

says i have

create table mytable( a VARCHAR(200) b VARCHAR(200) c VARCHAR(200) ) create index on mytable (b) 

if i choose

 select a, b, c from mytable; 

will it be sorted using b?

Answer 1

There are two main types of indexes: clustering and nonclustering.

The clustering index determines the physical order of the rows in the table. Inserting the table into the table (or updating the corresponding field) forces the database engine to reorder the data, so the field with the clustering index on it is sorted correctly. Therefore, in any table there can be only one clustering index.

Nonclustered indexes are copies of columns ordered as they wish. They exist separately, and the physical order of the lines is not related to them. Therefore, there can be as many non-clustering indexes as you want.

Most often, a simple selection in one table returns rows in physical order, so it would not be surprising to get them sorted by clustering index.

However, this is not guaranteed, and you should not rely on it. Always include the ORDER BY clause if the order of the result set is of any concern.

If you order by the clustering index, there is not much work for the database engine, but your intentions are clear.

If you order with a non-clustered index, the database requires a little more work, but (depending on the size of the table and data type) it will be an order of magnitude faster than ordering a completely non-indexed field.

Answer 2

It is possible (most likely, in the case of clustered indexes, I would introduce it), but you cannot rely on it or expect it. If you do not have order by , suppose it is not ordered.

Answer 3

You should never assume that the data returned by a query in RDBMS will be in any particular order. The only way to make sure the data is ordered is to explicitly request (usually an ORDER BY ) for the database engine to sort and organize the data returned by the request.

Answer 4

No, because you are not using the index in b in your query example.

This will use a clustered index scan or table scan.

And, as Kiren rightly points out, there is no implicit order in SQL. Even if you used the index in your query, the result may be affected by something that you have little control, such as internal joins (hash match, merge join, nested loops) that use the query mechanism.

If you want an ordered result, use ORDER BY .