- ASP.NET
- .NET Programming
- SQL
- C#
- Microsoft SQL Server
- Query Syntax
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!
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!
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.
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.
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)
products
ID int
data varbinary
author varchar(max)
date datetime
tags varchar(50)
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.
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.
Explore More ContentExplore courses, solutions, and other research materials related to this topic.
-
![]()
received a Solution
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM -
![]()
wrote an Article
Robust Field Cleaning Function -
![]()
created a Video
![]()
How to Bring Back the SQL Database from Suspect Mode to Normal Mode?
-
Explore Cloud Class® ![]()
Microsoft SQL Server and Database Concepts
Premium Content
You need an Expert Office subscription to comment.Start Free Trial