The query optimizer will look to see what's the quickest way (as far as it can tell) to get you your data.
It might be interesting to check the execution plan of the same query, with
ORDER BY [Id] added.
I'll assume that
Id is not only the primary key, but that its index is the clustered index on the table. What that means is that, to go through the index and pick out the
Id values, it'll have to read the entire table (because the clustered index is really the full table, sorted by the index key).
I'll also assume that
IX_SupplyProgramId has a limited number of other columns included. Note that all indexes will have the value of the clustered index, as that's how they connect the index back to the actual row. And, every value of
Id will be included
So, the amount of data that has to be read if it reads the clustered index is probably larger than the amount to read in the other index. Read are one of the most expensive operations the query engine does, so reducing reads is a good thing.
So, it uses the smaller index to get the
Id values, instead of the clustered index.