0

This question already has an answer here:

I have a table that does not have a primary key. This is because there is no unique field. So I created an index on SerNum, ScanDate so I could look at the table in SerNum, Scandate order. It appears as though the index is useless. When I select `WITH(INDEX ...) the results are not in the proper order. Why is this not working?

When I select with an order by SerNum, ScanDate I get

TR_SerNum   TR_Scan_Date    TR_Facility_Id  TR_Operation_Code
288470645   2015-12-04 14:39:08 95799   091
288470645   2015-12-04 18:15:53 95799   099
288470645   2015-12-06 00:42:07 95501   484
288470645   2015-12-06 23:43:54 95501   912
288470645   2015-12-07 00:16:54 95501   913
288474131   2015-11-04 06:14:37 27676   086
288474131   2015-11-29 12:38:42 73107   893
288474975   2015-11-08 13:23:13 13220   484
288474975   2015-11-09 20:30:50 13220   892
288476236   2015-09-13 01:39:44 60499   893
288476236   2015-09-13 16:27:17 60499   918
288476237   2015-08-31 18:39:45 48340   918
288476237   2015-08-31 20:00:50 48340   919
288476238   2015-09-04 12:35:19 36619   893

When I preform a select with index I get

TR_SerNum   TR_Scan_Date    TR_Facility_Id  TR_Operation_Code
288470645   2015-12-04 14:39:08 95799   091
288470645   2015-12-04 18:15:53 95799   099
288476300   2015-08-24 15:22:05 75260   918
288476300   2015-08-24 21:10:22 75260   919
288476315   2015-08-24 22:08:40 75260   918
288476862   2015-08-24 14:06:56 74141   918
288476311   2015-08-25 01:30:29 79402   919
288477203   2015-08-25 15:30:58 79402   918
288476402   2015-08-25 15:37:53 79402   918
288476515   2015-08-25 19:05:00 72231   919
288476647   2015-08-25 15:53:16 71102   919
288476522   2015-08-25 21:50:56 53714   918
288476399   2015-08-25 21:50:56 53714   918
|improve this question

marked as duplicate by mustaccio, ypercubeᵀᴹ, Tom V - Team Monica, Aaron Bertrand♦ sql-server Jun 30 '16 at 17:56

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 6
    The only way to guarantee the result ordering is to use the ORDER BY clause. – mustaccio Jun 30 '16 at 17:13
  • 1
    It's "not working" because you didn't tell SQL Server how you wanted the results ordered. As @mustaccio said, if you want a specific order, use ORDER BY. See #3 here: blogs.sqlsentry.com/aaronbertrand/… – Aaron Bertrand♦ Jun 30 '16 at 17:43
2

You confuse what with index means. Use of an index in no way directly drives the order of the results. A table does not have an inherent order. Even a table with a clustered index may not return results in the order of clustered index. Now a sort will use an index but the index alone does not force the sort. In your case the index made the sort efficient and I suspect if you look at the query plan the sort used the index.

|improve this answer

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