CODEWITHSUNDEEP
sqlindexing
dnuske
dnuske

Reputation: 578

does an index provide order by itself?

lets say I have 

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

create index on mytable (b)

if I select 

select a, b, c from mytable;

would it be sorted by b?

Upvotes: 4

Views: 149

Answers (4)

Tomalak
Tomalak

Reputation: 338228

There are two main types of indexes: clustering and non-clustering.

The clustering index determines physical row order in a table. Inserting into a table (or updating the respective field) causes the database engine to re-order the data so the field with the clustering index on it is sorted correctly. That's why there can only be one clustering index on any table.

Non-clustering indexes are copies of the columns, ordered as desired. They exist separately, and physical row order is not connected to them. That's why there can be as many non-clustering indexes as you want.

Most often a simple select on a single table returns the rows in physical order, so it would not be surprising to receive them sorted the way the the clustering index is.

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

If you order by the clustering index, there is not much work to do for the DB engine, but your intent is clear.

If you order by a non-clustering index, there is a little more work to do for the DB, but (depending on table size and data type) it will be orders of magnitude faster than ordering by an entirely unindexed field.

Upvotes: 2

Philip Kelley
Philip Kelley

Reputation: 40319

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

Upvotes: 5

JNK
JNK

Reputation: 65157

No, because you aren't using the index on b in your sample query.

That would use a clustered index scan or a table scan.

And, as Kieren rightly points out, there is no implicit order in SQL. Even if you did use an index in your query, result order can be affected by things you have little to no control over, like internal joins (hash match, merge join, nested loops) that the query engine uses.

If you want an ordered result, use ORDER BY.

Upvotes: 4

Kieren Johnstone
Kieren Johnstone

Reputation: 42003

Perhaps (more likely in the case of clustered indexes I would imagine), but you cannot rely on this or expect it. Unless you have an order by, assume it will not be ordered.

Upvotes: 6

Related Questions