is index used when using > or < ? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

is index used when using > or < ?

Excluding the work of the query optimizer deciding not to use an index based on excessive I/O, if I have a database field "price" and in my query use: select ….
from ….
where ….
and price > 200
and … is a non-clustered index on price used ? (the table with the price field contains between 30,000 and 150,000 records)
select ….
from ….
where ….
and price > 200
and price < 300
and … is a non-clustered index on price used ? (the table with the price field contains between 30,000 and 150,000 records) Thanks
Paul
Run set showplan_text on and run the query. It will tell you if it is using an index.
Tracking index usage helps you determine which indexes you need to keep, but you can’t get this information without a detrimental effect on your server performance. To get the information, run an audit-object trace on the object IDs that represent the indexes. You can also get the information by running a Showplan trace using SQL Server Profiler. In your case on the both the queries will use the non-clustered index on price.
You can check the execution plan for information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
From the information you’ve provided and in addition to what has been already said above, I would guess the index will be used. To verify open QA, hit Ctrl-K and run the query.
–Frank
http://www.insidesql.de
=<> comparisons will generally use indexes. Some things that will not use indexes (and I’m sure people can add to this) 1. When you aggregate or use a function on one of the columns you’re comparing. AND UPPER(s.name) = x.name for example would use an index on x.name, but not s.name. 2. Too small an amount of data. 3. Very small set of unique values, such as a bit field. 4. LIKE ‘%something’. Anytime you do a like with a wildcard at the beginning. This will use an index though: LIKE ‘some%’ MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
]]>