Strategies for checking ISNULL on varbinary fields?

In the past I've noted terrible performance when querying a varbinary(max) column. Understandable, but it also seems to happen when checking if it's null or not, and I was hoping the engine would instead take some shortcuts.

select top 100 * from Files where Content is null

I would suspect that it's slow because it's

  1. Needing to pull the whole binary out, and
  2. It's not indexed (varbinary can't be part of a normal index)

This question seems to disagree with my premise of slowness here, but I seem to have performance problems with binary fields time and time again.

One possible solution I thought of is to make a computed column that is indexed:

alter table Files
add ContentLength as ISNULL(DATALENGTH(Content),0) persisted

CREATE NONCLUSTERED INDEX [IX_Files_ContentLength] ON [dbo].[Files] 
(
    [ContentLength] ASC
)

select top 100 * from Files where ContentLength = 0

Is that a valid strategy? What other ways are there to efficiently query when binary fields are involved?

17
задан Community 23 May 2017 в 11:54
поделиться