How do I write a string search query that uses the non-clustered indexing I have in place?
I am looking to create a query that will use a non-clustered index plan on a street address field that is built with a non-clustered index. The problem I am having is that if I search for a street address, I will most likely use the "like" eval function. I think using this function will result in a table scan instead of using an index. How can I write a letter in this case? Is it just pointless to put a non-clustered index on the address3 field? Thanks in advance.
source to share
In theory, the database will use any index. What database server are you using, what are you really trying to achieve, and what would be your LIKE operator? For example, where wildcards can be relevant to the query plan being used.
Other possibilities, depending on what you want to achieve, do some preprocessing of the data and have other columns that are useful for your search, or using an indexed view.
Here are some discussions about using indexes with SQL Server 2005 and varchar fields.
source to share
If your LIKE expression searches at the beginning of a string ( Address LIKE 'Blah%'
), I would expect the index to be used, most likely using an index lookup.
If you are searching Address LIKE '%Blah%'
, a table / index scan will occur depending on how many fields you return in your query and how selective the index is.
source to share
varchar fields are indexed from left to right, just like a dictionary or encyclopedia is indexed.
If you knew where the field began (eg LIKE 'streetname%') then the index would be efficient. However, if you only know part of the field (eg LIKE "% something%"), then the index cannot be used.
source to share
Using LIKE will not necessarily use a table scan; it might use an index, depending on which row you are looking for. (For example, LIKE "something%" can usually use an index, whereas LIKE "% something" probably not, although the server may still be able to at least perform an index scan in that case, which is more expensive than direct index lookup, but still cheaper than full table scan.) There's a good article here that talks about LIKE versus indexes in relation to SQL Server (different DBMSs will implement it differently, obviously).
source to share