ms sql server; index on TEXT column

Question

I have a database table with several columns; most of them are VARCHAR(x) type columns, and some of these columns have an index on them so that I can search quickly for data inside it.

However, one of the columns is a TEXT column, because it contains a very large amount of data (23 kb of plain ascii text etc). I want to be able to search in that column (... WHERE col1 LIKE '%search string%'... ), but currently it's taking forever to perform the query. I know that the query is slow because of this column search because when I remove that critera from the WHERE clause the query completes (what I would consider), instantaneously.

I can't add an index on this column because that option is grayed out for that column in the index builder / wizard in ms sql studio.

What are my options here, to speed up the query search in that column?

Thanks for your time...

Update Ok, so I looked into the full text search and did all that stuff, and now I would like to run queries. However, when using "contains", it only accepts one word; what if I need an exact phrase? ... WHERE CONTAINS (col1, 'search phrase') ... throws an error.

Sorry, I'm new to ms sql...

Update 2 sorry, just figured it out; use multiple "contains" clauses instead of one clause with multiple words. Actually, this still doesn't get what I want (the exact phrase) it only makes sure that all words in the phrase are present.

Answer 1

You should be looking at using Full Text Indexing on the column.

Answer 2

Searching TEXT fields is always pretty slow. Give Full Text Search a try and see if that works better for you.

Answer 3

If your queries are like LIKE '%string%' (i. e. you search for a string inside a TEXT field), then you'll need a FULLTEXT index.

If you search for a substring in the beginning of the field (LIKE 'string%') and use SQL Server 2005 or higher, then you can convert your TEXT into a VARCHAR(MAX), create a computed column and index this column.

See this article in my blog for performance details:

• Indexing VARCHAR(MAX)

Answer 4

You can do complex boolean querying in FTS; like

contains(yourcol,'"My first sting" or "my second string" and "my third string"')

Depending on your query ContainsTable or freetexttable might give better results.

If you are connecting through .Net you might want to look at A google full text search