1

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.

10

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)
4

You dont need LIKE here.

In fact this will give you incorrect semantics as _ is a wild card for matching any single character. So your query would incorrectly exclude certain other strings too (though this is hypothetical as there is no wait type that it can clash with in practice).

One other possible approach to give your desired semantics is described here

select * 
from sys.dm_exec_requests 
where session_id > 55 
and Exists (SELECT wait_type EXCEPT SELECT 'BROKER_RECEIVE_WAITFOR') 
4

You'd have to add the additional check for null

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

You may get a better understanding of this situation by checking out the information in this link:

http://www.xaprb.com/blog/2006/05/18/why-null-never-compares-false-to-anything-in-sql/

To show nulls for only session_id > 55, you'd have to use parenthesis to force the correct boolean evaluation:

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

If you want to include nulls I would write it like this:

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

a number of times I've found optimizers handle COALESCE faster than or xxx is null

  • 2
    I'm not sure how a function would perform better than an or. Do you have evidence supporting your statement? – Tom V - try topanswers.xyz Aug 12 '16 at 19:56
  • 1
    @TomV - COALESCE is not a function -- it is a built in. The only evidence I can "give you" is my personal experience working with SQL for 20 years -- take it with a grain of salt or not as you wish. – Hogan Aug 12 '16 at 19:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.