策略上VARBINARY领域检查ISNULL？ [英] Strategies for checking ISNULL on varbinary fields?

问题描述

在过去查询一个VARBINARY（max）列时，我注意到糟糕的表现。可以理解的，但它似乎也检查时，如果是空或不是，我是希望发动机将改为采取一些捷径的情况发生。

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. 孤男寡女来拉动整个双星明和


2. 这不是索引（VARBINARY不能正常指数的一部分）

<一个href=\"http://stackoverflow.com/questions/2816096/efficiency-of-checking-for-null-varbinarymax-column\">This问题似乎这里缓慢的我的premise不同意，但我似乎有再次二进制领域的一次性能问题。

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?

推荐答案

我认为这是慢，因为VARBINARY列不是（并且不能）索引。因此，您使用一个计算（和索引）列的方法是有效的。

I think it's slow because the varbinary column is not (and can't be) indexed. Therefore, your approach to use a computed (and indexed) column is valid.

不过，我会使用 ISNULL（DATALENGTH（内容），-1）来代替，这样就可以长0和NULL区分。或者只是使用 DATALENGTH（内容）。我的意思是，微软SQL Server不是甲骨文，其中一个空字符串是一样的NULL。

However, I would use ISNULL(DATALENGTH(Content), -1) instead, so that you can distinguish between length 0 and NULL. Or just use DATALENGTH(Content). I mean, Microsoft SQL Server is not Oracle where an empty string is the same as NULL.

这篇关于策略上VARBINARY领域检查ISNULL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！