ChrisM@Work (10/8/2015)
Greg Edwards-268690 (10/8/2015)
NOT EXISTS, from what I understand, is a simple Boolean check.Aggregates must first gather all results.
I am not at all surprised it is generally faster.
Many times I found it to take a user query that churned, sometimes never finishing, into one that returned results very quickly.
A very good item to have in your toolbox.
Absolutely - but I've also seen Scott's suggestion of a derived table with aggregation to leave an EXISTS check in the dust. It depends on the shape of the data. EXISTS almost always wins when a) the searched column is a long way from unique or b) you have a small number of probes into a much larger probed table.
Exactly. In this case, when I looked at this, my knee-jerk reaction was no way is this faster. IO was going to be much higher. That being said, I suspect that it may perform on the actual data set as opposed to the example provided.
Once again, there are few absolutes and it really depends.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/