mrdenny   46765 pts.  |   Feb 17 2009  9:50PM GMT

A clustered index doesn’t actually change the order that the data is physically sorted in. It used to back in SQL 6.5, but it hasn’t since then.

I posted about the differences on my blog.

When the clustered index is built the indexed columns are sorted within each page in the correct order just like a normal index, however the clustered index points back to the heap (the table) for the other values.

Row level data within the table is sorted in the order it is written to the data page which is why the only way to guarantee order is to use the ORDER BY.

Whateverelse   25 pts.  |   Feb 18 2009  3:56PM GMT

You did not say which database you are using. I defer to Mr. Denny regarding SQL Server, but other databases do things different. In Informix, for example, you can run ALTER INDEX … TO CLUSTER. This will cause the table to be reorganized immediately, with the rows stored in the order that matches the key of the index that was ALTERed.

In DB2, a clustering index attempts to maintain clustering even as new rows are inserted. This can lead to page splits when the inserted row belongs on a page that is already full. These splits used to be called “near-off” or “far-off” splits (don’t know if that terminology is still in use — it’s been a while), depending on whether there was an empty page (see FREEPAGE parameter) nearby that could be used to hold the data split from the original page. Additionally, when the table is REORGed, these splits will be consolidated and the rows will be placed in CLUSTER key order.

I do not know if Oracle has anything comparable.

Carlosdl   29770 pts.  |   Feb 19 2009  5:33PM GMT

I think the closest thing in Oracle would be Index Organized Tables (IOT).

Sivu   105 pts.  |   Feb 20 2009  7:02AM GMT

Hi Amin,

Clustered index sorts the records accordingly and stores in the table.

Non-Clustered index is not physically ordered but ordered its offset.

Regards,
Siva