Efficiency of checking for null varbinary(max) column ?
Posted
by Moe Sisko
on Stack Overflow
See other posts from Stack Overflow
or by Moe Sisko
Published on 2010-05-12T04:18:55Z
Indexed on
2010/05/12
4:24 UTC
Read the original article
Hit count: 365
sql-server-2008
|tsql
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).
© Stack Overflow or respective owner
