By default the database creates an index on a key column (Primary Key). Its up to the programmer to create an index on a non-key column.
The presence of keys in a table does not matter for the ordering of retrieval. Until and unless the ordering is mentioned in the SELECT statement it is NOT considered.
As the database maintains the rows in "heap-ordered" and NOT "index-ordered" and if you dont specify any specific ordering in the SELECT statement, the ordering is purely upto the database. Its not guaranteed!
Hope this helps! [ August 10, 2007: Message edited by: Raghavan Muthu ]
You're right always try to have an explicit order, but when I don't provide one my customers always complain that there is no order and I always see that the records are ordered in a way they are updated.
This behavior could arise by the use of temp tables in my stored procedures and where mostly is no index available.
I did a test for a direct select in Sybase, Mysql and Oracle and could see that indeed by defining a primary key the records are ordered that way. I think this is because for the database is more natural to retrieve data ordered by their primary key.
Originally posted by Remko Strating: I think this is because for the database is more natural to retrieve data ordered by their primary key.
Though it could possibly be tentatively-correct but not exactly. Because the data in the *physical file* is retrieved in the order they reside in the blocks. But we can't take it for granted that it is guaranteed.
In precise, simply having a PK defined for the table really does not matter until its used in the order by column in the SELECT clause to have a real effect. [ August 14, 2007: Message edited by: Raghavan Muthu ]