2

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

  • 4
    There is absolutely positively no way to guarantee order without an order by. – Erik Darling Nov 13 '16 at 0:06
  • 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. – andrea Nov 13 '16 at 0:18
  • Yeah, allocation order is most likely, but again it's not guaranteed. – Erik Darling Nov 13 '16 at 0:31
3

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/

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.