expensive cast operation?

I need some tips to make the following cast operation more efficient. The
idea is to compare a textual value stored as an image data type (don't ask
why:). The text will never be more than 20 characters.
Is it an idea to set the size of the varbinary?
...
CAST(CAST(f.data AS VARBINARY) AS NVARCHAR(20)) = '123456789'
/tedTed,
CAST is very efficient. Is it possible you want to optimize
a query that contains this comparison? As written, an index
on f.data will be useless. If you cast the literal string, on the
other hand, an index can be used.
If my guess is right, see if this works any better for you:
f.data = cast('123456789' as varbinary(20))
I believe this will be true if f.data matches but has trailing 0x00's,
i.e., is one of the following:
0x313233343536373839
0x31323334353637383900
0x3132333435363738390000
...
Hopefully that doesn't matter, but you could add a check:
f.data = cast('123456789' as varbinary(20))
and datalength(f.data) = datalen('123456789')
Steve Kass
Drew University
ted wrote:

>I need some tips to make the following cast operation more efficient. The
>idea is to compare a textual value stored as an image data type (don't ask
>why:). The text will never be more than 20 characters.
>Is it an idea to set the size of the varbinary?
>...
>CAST(CAST(f.data AS VARBINARY) AS NVARCHAR(20)) = '123456789'
>/ted
>
>|||The following query gives med an error. So I guess the values cannot be
compared this way. "Like" does not give any result either.
SELECT * FROM ctbl_file f where f.data = cast('80196' as varbinary(20))
"The text, ntext, and image data types cannot be compared or sorted, except
when using IS NULL or LIKE operator."
"Steve Kass" <skass@.drew.edu> skrev i melding
news:O3gT6eGKFHA.732@.TK2MSFTNGP12.phx.gbl...
> Ted,
> CAST is very efficient. Is it possible you want to optimize
> a query that contains this comparison? As written, an index
> on f.data will be useless. If you cast the literal string, on the
> other hand, an index can be used.
> If my guess is right, see if this works any better for you:
> f.data = cast('123456789' as varbinary(20))
> I believe this will be true if f.data matches but has trailing 0x00's,
> i.e., is one of the following:
> 0x313233343536373839
> 0x31323334353637383900
> 0x3132333435363738390000
> ...
> Hopefully that doesn't matter, but you could add a check:
> f.data = cast('123456789' as varbinary(20))
> and datalength(f.data) = datalen('123456789')
> Steve Kass
> Drew University
> ted wrote:
>|||Oops You probably can't make this much faster without a non-image binary co
lum=
n. You can add substring to the left side of what I have, but it may be no b
ett=
er than before.
S