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?)

more ▼

asked Jan 15, 2010 at 03:24 PM in Default

avatar image

Aaron Bush
1 1 1 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Jan 15, 2010 at 03:33 PM

avatar image

dave ballantyne
928 1 3 6

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

Jan 15, 2010 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, 2010 at 06:23 PM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

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:



more ▼

answered Jan 18, 2010 at 06:38 AM

avatar image

Håkan Winther
16.6k 38 46 58

(comments are locked)
10|1200 characters needed characters left

I would agree with our friends. Before I made a comment I would like to see you T-SQL statement. Can you please share your query with us?


more ▼

answered Jan 16, 2010 at 04:01 AM

avatar image


(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 15, 2010 at 03:24 PM

Seen: 2237 times

Last Updated: Jan 15, 2010 at 03:24 PM

Copyright 2018 Redgate Software. Privacy Policy