(where) is a field of type text

is there any way to optimize a single query; but whose condition (where) is a field of type text, for being such a field can not create indexes and as the field is so large, it takes a lot a simple query.
Who is Participating?
Gerwin Jansen, EE MVEConnect With a Mentor Topic Advisor Commented:
Creating one or more extra columns that contain a substring of what you want to search for (where) is a possibility. Create an index on the extra columns, upon inserting add the substring(s) to the extra columns. Will cost you some storage and performance on inserting, so depending on your data, amount of inserts/queries you will get better performance.
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Shows us your query, and some sample WHERE conditions.
Indexes can be created on text fields, but it is not considered a best practice due to the larger size of text data types compared to numeric.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Give us an example, please.
Scott PletcherSenior DBACommented:
If there are "canned" searches, you could also pre-compute flags and then simply check the flags.

Specifically which version of SQL, 2005 or 2008 or other?  There are indexing differences between/among the versions.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.