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
ORDER BY
clause. – mustaccio Jun 30 '16 at 17:13