I have put 1 primary key on id column and 1 non clustered index on NAME column but when I retrieve the the table it is showing the data in an unsorted order .I think it is sorting through non clustered index where as we have the rule that non clustered doesn't sort the data (Like-1003,1001,1005,1002,1004)why so ? so what may be the possible reason!!!! Thanks in advance...
The only way to guarantee the order of the data you request is to add an ORDER BY to your query. Just because there is a Clustered Index on a table doesnt imply that data will always be returned in that order. It often is but there is no guarantee. Do not rely on this quirk. Tell SQL Server you want the data in order and you will get it in order.
I believe that unless you include an ORDER BY clause in your query there is no guarantee that SQL will return the data in any particular order no matter what indexes are on the table. If you need the results in order then you need to specify that in the query.
If you have no ORDER BY in your statement the data will be retrieved by the order of the clustered index. This is the physical order of the relation. Keep in mind that clustered index IS the relation. If you don't have a clustered index you have a heap.
Thomas, Fatherjack, that's correct but a view to the execution plan will show what Microsoft SQL Server will do. It if is (in 99.99999%) a clustered index scan it IS the physical order of the relation. Without seeing the script of the relation and seeing the implemented indexes I doubt about the ordered output of the relation.