I think i found the root cause here. The query which we were using brings top 15 records in asc order based on a column which has NULL or 0 0r 1.
Right, so ordering by a non-unique column and limiting the results. That's the case I showed to opc. Because that column's not unique, when you order by just that you have no guarantee of what order the rows will be within the 'null, 0 or 1' groupings, hence the index changes the assumed order (because it was never guaranteed) and appears to change the results.
The solution here would be to order by a second column that does define the 'correct' order of rows as you want to see them. Removing the column from the index is hiding the symptom, nothing more. There are lots of other things that can and will cause the order of rows to differ from what you assume they should be.
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass