Full text search of varbinary column

I'm looking for suggestions on the best approach for creating a full text
search index on a varbinary column. The varbinary data is the binary
serialization of a custom clr datatype (but to be clear -- the column is
typed varbinary, not the custom type).
I thought I might be able to create a computed column that converted the
varbinary to the custom type, then used methods on the custom type to convert
to string. But it seems like I can't do that without persisting the computed
column which I really don't want to do.
Another approach I'm considering is to create an iFilter for the datatype,
then create a computed column that just returns the column type I'd register
for the iFilter. That just seems a little heavy weight to me and has
deployment headaches.
I can't help think I'm missing something obvious. Any ideas?
Thanks.
You could create the ifilter and have it emit the text data which is stored
in the varbinary column. I think you will find the overhead of this to be
significant, and would advise you to store the data as text data in a
varchar column and index that column.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Geoff Chappell" <GeoffChappell@.discussions.microsoft.com> wrote in message
news:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
> I'm looking for suggestions on the best approach for creating a full text
> search index on a varbinary column. The varbinary data is the binary
> serialization of a custom clr datatype (but to be clear -- the column is
> typed varbinary, not the custom type).
> I thought I might be able to create a computed column that converted the
> varbinary to the custom type, then used methods on the custom type to
> convert
> to string. But it seems like I can't do that without persisting the
> computed
> column which I really don't want to do.
> Another approach I'm considering is to create an iFilter for the datatype,
> then create a computed column that just returns the column type I'd
> register
> for the iFilter. That just seems a little heavy weight to me and has
> deployment headaches.
> I can't help think I'm missing something obvious. Any ideas?
> Thanks.
|||Thanks. I'm leaning towards the approach you suggest.
It seems like it comes down to a space/time tradeoff to some extent -- I can
either take the hit of the space required to materialize an alternate
representation (e.g. persisted computed column, indexed view, etc.), or pay
the runtime costs of an ifilter (there's also a time cost to any
materialization, and a complexity cost to the ifilter so it's not quite that
simple).
One of the things that bugs me about the ifilter approach is that it seems
like a hack since I have to fake a document extension for my custom datatype
to make it work. Do you know if any other values are accepted for Column
Type? e.g. can I just use the clsid of the filter?
-Geoff
"Hilary Cotter" wrote:

> You could create the ifilter and have it emit the text data which is stored
> in the varbinary column. I think you will find the overhead of this to be
> significant, and would advise you to store the data as text data in a
> varchar column and index that column.
> --
> http://www.zetainteractive.com - Shift Happens!
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Geoff Chappell" <GeoffChappell@.discussions.microsoft.com> wrote in message
> news:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
>
>