Indexing a Binary column

Last Post 21 Feb 2008 05:15 PM by ddavidson340. 6 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
ddavidson340
New Member
New Member

--
20 Feb 2008 05:14 PM
We have a table that stores binary images in a SQL table. Wondering if anyone knows of a way to index a binary column?

We need to be able to search on the binary column that could contain upto 100K rows. 100K rows is nothing for a table that has an index, but we without an index on a binary column it becomes a sequential search and takes a couple minutes to search.

Does anyone know if SQL 2008 will permit an index on a binary column ??

Thanks for your help
Don Davidson
SQLUSA
New Member
New Member

--
20 Feb 2008 05:17 PM
What is in it?

What would be the subject of indexing?

How would search be carried out?

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005highperformance/ The Best SQL Server 2005 Training in the World!
SQLUSA
New Member
New Member

--
21 Feb 2008 01:37 AM

<b>If your data is Word document/Excel spreadsheet for example, you can create a FULL-TEXT index on it.

Column type nvarbinary(max), IFILTER feature is being used.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005highperformance/ The Best SQL Server 2005 Training in the World! </b>
ddavidson340
New Member
New Member

--
21 Feb 2008 05:06 AM
We are capturing finger prints as a Binary image in a MS SQL database for a Law Enforcement purposes. When someone is booked in Jail, or needs to enter the Jail for any other purpose, their prints are taken and used to expidite the process of checkin the next time they need to enter the Jail. Because inmates are not known for being totally truthful on their name, we have occasions where people are booked under the wrong name. When someone reenters the Jail, the system searches the database for a matching prints, returns all possible matches.

The prints are being saved from a third party OCX. they OCX does not provide a method to index the print so we have to rely on SQL for a Sequential lookup of the table.

Don Davidson
SQLUSA
New Member
New Member

--
21 Feb 2008 08:55 AM
What do you want the index on?

Name of image? Possible aliases?

You can also just store the name and possible aliases, the name of the image file in the database and the images in Windows file system. You can use normal SQL queries on the names and aliases.

If you store word processing documents in the database as varbinary(max), they can be full-text indexed and you can use full-text search.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005grandslam/ The Best SQL Server 2005 Training in the World!
ddavidson340
New Member
New Member

--
21 Feb 2008 05:15 PM
I really appreciate everyones comments...!! I hope that 2008 may help us with the problem. I spoke to another developer at work today about this problem. He is familiar with the OCX methods that are exposed and there is nothing that the OCX makes available that we could index.... The vendor does have another solution, but it is VERY expensive. They charge by the finger print that is stored in SQL.

The Federal Government has a Finger Print solution that is funded as part of the Department of Homeland Security. They are pushing it to be an open standard and it is indexable, as the prints are put in categoried before being saved. Subsequent searches for a print are scanned then indexed to reduce the search criteria. May have to start from scratch and redevelop.

As far as being reliable, the current system is VERY reliable. In fact you can capture partial prints. Maybe around 75 percent of the finger and it can still be locaed in the DB. Can even capture and compare prints up side down and 90 degrees and still be found. Remember its only a binary file and it searches for patterns.

Someone suggested that we store the image in a file. It is not possible with the OCX that we are using....
Have not been to Orlando for anything except to take the family to Mickey Land a few years back....

Thanks,
SQLUSA
New Member
New Member

--
22 Feb 2008 01:02 AM
> The vendor does have another solution, but it is VERY expensive. They charge by the finger print that is stored in SQL.


As usual.... good software demands high price....


SQL Server 2008 has a new datatype: GEOMETRY . It appears that this is the way for you to go, because there are no functions available for general images.

On the other hand you can FULL TEXT INDEX a word document if you associate it with a fingerprint in varbinary(max) data type column.

That means that you would have to enter the keywords into the Word document to be used for FULL-TEXT SEARCH.

The vendor does have another solution, but it is VERY expensive. They charge by the finger print that is stored in SQL.



Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005/ The Best SQL Server 2005 Training in the World!





Acceptable Use Policy
---