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 criteria 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 SQL Server Management 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 SQL Server

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

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

Answer 2

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)