The thing is, SQL Server still needs to get all of those rows from index. Job done in first case, but on the second: Given loop join, for EVERY of those rows re

The thing is, SQL Server still needs to get all of those rows from index. Job done in first case, but on the second: Given loop join, for EVERY of those rows returned you must now lookup clustered index row and see if b matches the case or not. Each lookup may take few logical reads, depending on index depth, which depends on table size. Say you have 5 logical reads for tables with millions of entries to lookup clustered index row. Multiply returned rows which still must be filtered by b. If you get 200 rows, then 200*5 would be 1000 additional logical reads. The result is the lower the selectivity for parameter a, the slower is the query (orders of magnitudes slower). For a low execution count query it doesn't matter that much, but if that one gets executed hundreds or thousands of times per minute, well, better to have b column either in index key column or included column - that would dramatically speed up things.