Will this make my query faster or slower?

I have a table where the data I really want is being stored in a text field (which I am getting use "Like"). However you can filter to a super-set of that data with a straight number comparison. If I have both filters in my where clause, do I have any control over the order in which the filters are applied. (IE: Have it filter to numbers then do the text search?)

asked Jan 15 '10 at 03:24 PM in Default

Aaron Bush
1 ● 1 ● 1 ● 1

(comments are locked)

3 answers:

It is the optimizers job to decide which is the fastest method to return the data. So no you dont get control directly , but you will influence the decisions it makes by using indexes etc.

answered Jan 15 '10 at 03:33 PM

dave ballantyne
918 ● 1 ● 1 ● 4

Agreed. If you index that number field, the optimizer will undoubtedly choose it over scanning a text field. I know I would. :)

Jan 15 '10 at 05:15 PM Tom Staab

It depends! The index on the INT column will only be used if the number field is selective enough. I am sure sql wouldn't use the index if it contains only two distinct values.

Jan 18 '10 at 06:23 PM Håkan Winther

(comments are locked)

Actually you can force a specific execution plan in at least two ways:

with a table hint "INDEX ( index_value [ ,...n ] )"
forced execution plan with "USE PLAN N'xml_plan'" (SQL server 2008)

But i do not recommend you to try to force SQL to ignore the "optimizer" unless you know you are much more intellegent than the developers of the SQL optimizer. It is easy to think that this execution plan is the best, but are you sure? What will happen when your data is changed? Maybe your number column is not selective enough and you force it to use that index? Then you end up with an execution plan that is outperformed of other possible execution plans that SQL optimizer wants to use.

for more information see:

http://msdn.microsoft.com/en-us/library/cc917694.aspx

http://technet.microsoft.com/en-us/library/ms187373.aspx

answered Jan 18 '10 at 06:38 AM