It looks like an "IS NULL" predicate, when applied to a LOB field,
causes that LOB's pages to be read.
This results in a very slow processing of queries like:
SELECT
(case when [myColumn] is not null then 1 else 0 end) as test1
FROM myTable
Specifically, this behaviour can be observed on SQL2005 and IMAGE
datatype.
Theoretically, I would assume that SQL Server should simply check a
NULL bitmap in the cases above, but it performs "lob logical reads"
instead.
The above can be observed in "set statistics io on" mode.
So I wonder if there is a "fast" way to check if the LOB value contains
NULL without the need to read LOB pages.
Thanks for comments/ideas.
--
Vladislav
>> Stay informed about: ""IS NULL"" check applied to a LOB field causes ""lob logical..