Returning value with Clustered index

Question

I have emp table (empno, ename, job, mgr, hire_date, sal, deptno) with no index at all. If i run

select * from emp 

i get the rows ordered by empno. Now i create a clustered index like this:

Create clustered index name on emp(ename)

If i run select * again, the rows are now ordered by ename. Now if i drop the index.

drop index name on emp

I would expect to see the rows ordered back by empno, like before creating the index. Instead I still see the rows ordered by ename, as if the index were still there. Is there a way to see the rows ordered back by empno as before without using "order by empno?". I tried to log off and connect again to make a refresh, but did not work. The only way was to drop and recreate the table. Maybe is there a parameter in the table definition that is changed when the index was created, and it is not updated when the index is deleted?

It seems to me that there is a default method for displaying the data before the index, which is not preserved / restored when the index is deleted

Answer 1

When you created the clustered index with ename the rows in the table were shuffled to be in that order. When you dropped the index the rows were not reshuffled.

There is no guarantee that the rows will be returned by a query in the order that they are stored, but often this is what happens.

If the order is important then you would add an ORDER BY (and would be well advised to cluster the table on that field too to avoid sorting every time).

But don't choose a clustering key just because of that. For a more thorough discussion of clustering and heaps see http://kejser.org/clustered-indexes-vs-heaps/

Answer 2

This article from SQL Server architect Conor Cunningham pretty much sum this topic up.

Conor says...

If you need order in your query results, put in an ORDER BY. It’s that simple. Anything else is like riding in a car without a seatbelt.