Solved

(where) is a field of type text

Posted on 2014-12-23
5
123 Views
Last Modified: 2014-12-26
experts,
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.
0
Comment
Question by:enrique_aeo
5 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 40515503
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.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40516099
Give us an example, please.
0
 
LVL 37

Accepted Solution

by:
Gerwin Jansen earned 250 total points
ID: 40516296
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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40516676
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.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now