CODEWITHSUNDEEP
sql-servernullvarbinary
Juninho Bill
Juninho Bill

Reputation: 27

MSSQL - Select NULL values on varbinary column

I am trying to do a select on a table with around 35.000 rows, one of the columns is a varbinary with may have some NULL values and I need to exclude those NULL values from my results.

The problem is: when you do a select on a varbinary column, it takes a lot of time to complete, I've found a way that I'm not sure I can use or is the best way to do something like that and I would like some opinions.

Here is it:

SELECT REQUEST,REQLOCATION,DESCRIPT,BLOBNAME,BLOBSIZE,substring(BLOBVALUE,0,1) AS BLOBVALUE,BLOBMODE,BLOBPATH,BLOBID,
REDIRID,ANALYST,CLIENT,SEVENT,PACKAGE,INSERTDATE
FROM REQBLOB WHERE substring(BLOBVALUE,0,1) IS NOT NULL

The varbinary column is the "BLOBVALUE" one where I do a "substring" select and this query gave me a result of 20.000 rows instantly and I think it's returned only valid data, not NULLs, what you think about that?

Upvotes: 0

Views: 1061

Answers (1)

Thom A
Thom A

Reputation: 95830

Get rid of the SUBSTRING in the WHERE, it's making your query non-SARGable; that's why it's slow.

SELECT REQUEST,
       REQLOCATION,
       DESCRIPT,
       BLOBNAME,
       BLOBSIZE,
       SUBSTRING(BLOBVALUE, 0, 1) AS BLOBVALUE,
       BLOBMODE,
       BLOBPATH,
       BLOBID,
       REDIRID,
       ANALYST,
       CLIENT,
       SEVENT,
       PACKAGE,
       INSERTDATE
FROM REQBLOB
WHERE BLOBVALUE IS NOT NULL;

Why are you using substring on a varbinary anyway though..?

Upvotes: 2

Related Questions