We help IT Professionals succeed at work.

Searching varbinary field quickly

SQL Server
ASP.NET
I have a varbinary field that stores the content of various documents such as pdfs, docs, csvs, etc.

What is the best method for searching the varbinary field quickly?

Does indexing it help? etc.?

thanks!
Comment
Watch Question

CERTIFIED EXPERT
Commented:
Where would you like to search the field? On SQL Server or on the client?

SQL Server:
Indexing does not help here. Fulltext index is an exception. But you have to be aware of certain drawbacks, e.g. when the varbinary field contains Unicode or UTF-8 data you have to search for exact binary representation of such data. The index itself should have LANGUAGE 0 to be language independent. This, of course, disqualifies certain language dependent searches which are possible when the text is stored in (n)varchar. You can read more e.g. here: https://www.enabledbusinesssolutions.com/blogs/full-text-search-on-varbinary-max-columns-and-using-the-right-filters/

Client:
The main disadvantage of such solution is the necessity to read the whole binary field into the client (over the network obviously). Then you may use various tools helping you to search in given file type in the client application in C#, VB.NET, or any other language used.
David Johnson, CDSimple Geek from the '70s
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
you can't you add the properties you want to search on in other areas of the table that point to the varbinary i.e.
products
ID int
data varbinary
author varchar(max)
date datetime
tags varchar(50)
Starr DuskkASP.NET VB.NET Developer

Author

Commented:
Sorry, yes, I intend to search using a SQL Query, not client side.

We have found we can search these results if the file is csv and return rows, and some pdfs show up, but other pdfs do not find the asked for data.
CERTIFIED EXPERT

Commented:
Some PDFs do not contain text info in open text and/or they can have just image format. In such case you would need OCR to read them which is hard to implement on the SQL Server.
Starr DuskkASP.NET VB.NET Developer

Author

Commented:
Thanks pcelba! With your advice, we were able to make it work. Great answer!