I&#039;m working on updating a document search application that a developer who has since left the organization created. The current implementation uses "WHERE docText LIKE &#039;%search term%&#039;" to find matches, and the docText column is just a copy of the text in the document (Word or PDF, generally). There&#039;s a column pointing to the location of the actual document.<BR><BR>This has created two problems. First, users are complaining that the results they&#039;re getting are worthless. LIKE obviously doesn&#039;t rank results by relevance, and since the documents being searched generally cover overlapping topics, this is important. A document that contains a search term 50 times needs to be displayed higher than one that contains it once. The second problem is that the content maintenance people hate having to open up the document in Word or Acrobat, copy out the text, and paste it into the text field.<BR><BR>The solution to the first problem would definitely seem to be making use of SQL Server 2000&#039;s full-text search functionality. It would be relatively easy to index the docText column and search that, but that doesn&#039;t solve the second problem. I know that SQL Server supports full-text indexing of supported file types (including Word out of the box and PDF through an add-on) via the IFilter interface, so that&#039;s the direction I&#039;m looking in.<BR><BR>I considered using MS Indexing Server to do a full-text query directly on the file system. The problem is that the web server, where the documents are stored, is on a different machine than the SQL server. There might be a way to deal with this using Linked Servers, but that sounds too complicated and I&#039;m not sure I want to try it unless necessary. Since I need to combine the search results with other fields in the database, I can&#039;t bypass SQL server and do an Index Server search directly on the web sever.<BR><BR>My next thought was to store the files as a blob in an image column directly in the database. However, this would result in a lot of data being thrown into the database, and I&#039;m not sure what all the ramifications of that are. Right now, all the documents come out to about 60mb (some of which are duplicates and can be removed), and unless the usage model changes drastically, I can&#039;t see this total increasing by more than 30mb or so a year. The issue is not storage space--we can afford to dedicate a couple hundred megabytes on the SQL server to these documents. However, are there performance issues I should be concerned about, and if so, how significant are they?<BR><BR>If this approach causes too many problems, I can always tell the content maintenance people that they&#039;ll have to continue copying the text in directly, but I&#039;d like to find a workaround if possible. Any guidance would be very much appreciated.<BR><BR>Thanks,<BR>-Brian