SQL Server index in the TEXT column

Question

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

However, one of the columns is the TEXT column, because it contains a very large amount of data (23 kb plain text ascii, etc.). I want to be able to search in this column (... WHERE col1 LIKE '%search string%'... ), but currently it accepts query execution forever. I know that the query is slow because of this column search, because when I remove these criteria from the WHERE , the query ends (which I will consider) instantly.

I cannot add an index to this column because this option is grayed out for this column in the index builder / wizard in SQL Server Management Studio.

What are my options here to speed up the search for queries in this column?

Thank you for your time...

Update
So, I looked at the full text search and did all this, and now I would like to run queries. However, when using "contains", it takes only one word; what if i need the 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" sentences instead of a single sentence with multiple words. In fact, it still doesn’t work out what I want (exact phrase), it only ensures that all the words in the phrase are present.

Answer 1

Finding TEXT fields is always quite slow. Give Full Text Search a try and see if this works best for you.

Answer 2

You should look at using full-text indexing in a column.

Answer 3

If your queries look like LIKE '%string%' (for example, you are looking for a string inside a TEXT ), you will need the FULLTEXT index.

If you are looking for a substring at the beginning for a field ( LIKE 'string%' ) and using SQL Server 2005 or higher, you can convert TEXT to VARCHAR(MAX) , create a computed column and the index of that column.

See this blog post for performance details:

• VARCHAR Indexing (MAX)

Answer 4

You can execute complex boolean queries in FTS; as

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

Depending on your request, ContainsTable or freetexttable may give better results.

If you are connecting via .Net, you can see google full text search

Answer 5

And since no one has already said (perhaps because it is obvious), the LIKE '%string%' query LIKE '%string%' bypasses your existing indexes, so it will work slowly. Therefore, why you need to use full-text indexing. (as Kvasnoy said).

Correction - I'm sure I found out about this and always believed in it, but after some investigation (using wildcards in the beginning) it seems OK? My old regular expression queries work better with cute ones!