StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Server; index on TEXT column
text
Body
copied!<p>I have a database table with several columns; most of them are <code>VARCHAR(x)</code> type columns, and some of these columns have an index on them so that I can search quickly for data inside it. </p> <p>However, one of the columns is a <code>TEXT</code> 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 (... <code>WHERE col1 LIKE '%search string%'...</code> ), 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 <code>WHERE</code> clause the query completes (what I would consider), instantaneously. </p> <p>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.</p> <p>What are my options here, to speed up the query search in that column?</p> <p>Thanks for your time...</p> <p><strong>Update</strong><br> 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? <code>... WHERE CONTAINS (col1, 'search phrase') ...</code> throws an error.</p> <p>Sorry, I'm new to SQL Server</p> <p><strong>Update 2</strong> 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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload