suji

 Profile Answers by suji Questions by suji

• Nov 7th, 2011

 

I would use full-text indexing as you are on the latest version. Create and store the html in varbinary(max) column and set its associated file type to ".html" in a file type column. Then enable full-text index, so indexer will parse the data and search only the text content while ignoring the HTML tags. We had been using blob to store the documents, with the new features have come out in the later versions, this seems to be best option. We are in the process of migrating one of the app to use varbinary and to take advantage of the search feature.

There is another way of storing using filestrem. Here is a good read-up from MS .

FILESTREAM enables SQL Server-based applications to store unstructured data, such as documents and images, on the file system. Applications can leverage the rich streaming APIs and performance of the file system and at the same time maintain transactional consistency between the unstructured data and corresponding structured data

http://blogs.msdn.com/b/manisblog/archive/2007/10/21/filestream-data-type-sql-server-2008.aspx

Here is another old report from MS back in 2006 explains about using oldway of BLOG

http://research.microsoft.com/apps/pubs/default.aspx?id=64525

There are multiple options for you, pick the best approach that matches your needs

Thanks,
Suji