Use of nonclustered index 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
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.