Sql-server – SQL server not listing records with NULL values

sql serversql-server-2008-r2

I faced a strange phenomenon today. I ran the below query to Find all requests with waits other than BROKER_RECEIVE_WAITFOR

select * from sys.dm_exec_requests where session_id > 55 
and wait_type not like ('BROKER_RECEIVE_WAITFOR')

The query ran fine, But it failed to list any request with No waits, ie with wait_type as NULL(there were quite a lot of running queries without any waits at this time).

Why is this happening, Isn't it supposed to list all records with wait_type not like 'BROKER_RECEIVE_WAITFOR' including Nulls?

I'm running SQL server 2008R2 SP3.

Best Answer

The reason for this is that a NULL represents an unknown value.

NULL is not LIKE anything and not NOT LIKE anything either.

Try running the following 2 statements on your system:

SELECT CASE WHEN null like '%a%' THEN 'true' ELSE 'false' END;
SELECT CASE WHEN null not like '%a%' THEN 'true' ELSE 'false' END;

That should return false for both statements.

So your query should look like this:

select * from sys.dm_exec_requests where session_id > 55 
and (wait_type not like ('BROKER_RECEIVE_WAITFOR') or wait_type is null)
Related Question