T-SQL IS NOT NULL vs NOT NULL performance
comparison, null, sql-server, t-sql
Solution
Both can be carried out as an index seek.
One of them seeking into the beginning of the index reading all the rows with `NULL` then stopping when it encounters the first `NOT NULL` and the other one seeking into the first `NOT NULL` value then reading the entire rest of the index.
Unless the index is covering however a seek may not be used in either case as it comes down to selectivity. If you have many `NULL` values so `NOT NULL` is highly selective you might want to consider creating a filtered index on that column as per the example here.
Problem
I have read a lot of not using negative operators like NOT IN and NOT EXISTS and how reversing your logic to positive operators, for example using IN instead NOT IN could lead to very good performance. Anyway, I have a view with a lot of CASE WHEN operators and checking some conditions. I have wondered (still I have been able to find any article, question or example specific for this) will be a performance difference in using IS NOT NULL and IS NULL?