Efficiency of checking for null varbinary(max) column ?

Question

Using SQL Server 2008.

Example table :

CREATE table dbo.blobtest
(id int primary key not null,
name nvarchar(200) not null, 
data varbinary(max) null)

Example query :

select id, name, 
cast((case when data is null then 0 else 1 end) as bit) as DataExists 
from dbo.blobtest 

Now, the query needs to return a "DataExists" column, that returns 0 if the blob is null, else 1.

This all works fine, but I'm wondering how efficient it is. i.e. does SQL server need to read in the whole blob to its memory, or is there some optimization so that it just does enough reads to figure out if the blob is null or not ?

(FWIW, the sp_tableoption "large value types out of row" option is set to OFF for this example).

Answer 1

It uses the NULL bitmap. So no.

Answer 2

Actually it can not read the blob because there is no blob to start with ;)