0

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
2
  • 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.