TSQL-What is difference between two queries

I have a table with column isprocessed bit null. I have created an index(non clustered) on this column. Before applying index query

**`SELECT top 5000 * From Request Where IsProcessed Is Null`**

took 30 to 40 seconds. Remember this is very huge table with rows over 10 million. Now after creating index on isproce开发者_如何学编程ssed column same query has shown no performance boost.

I tried another query **

;With TopN As(SELECT Top 5000 * from Request Order By IsProcessed)
SELECT * From TopN Where IsProcessed Is Null

** Now this query suprizingley gives output in less than 2 seconds I wonder why is there any perforamnce difference in two queries. Also what is the indexing behavior on bit null columns

Turn on 'Show Actual Execution Plan' to see which indexes are being used in each case.

If you create an index on a column with low selectivity (such as a bit field), the optimiser will probably not use it (it depends).

If you have an 'IsProcessed' bit column and there is less than 10% of rows with it set, and you search for those set, then the index might be used. Whereas, if you have an index on a 2-state field such as gender ('M', 'F') with roughly 50% / 50% values, then it is highly unlikely that the optimiser will use that index.

The second query will select top 500 first, and then apply the where clause, where the first query will apply the where clause first and then the top 5000

Have a look at SQL SERVER – Logical Query Processing Phases – Order of Statement Execution