TSQL-What is difference between two queries

Question

Hi, 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 isprocessed 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

Answer 1

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.

Answer 2

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