using an index withe ">"
-
if i have a table where the date is an index
does retrieving rows like the query below disregard the index?
select * from table where date > getdate()
i know that using OR disregards the index (eg: select * from table where date = x or date = y ) , but what about > ?
tx!
-
Among others it depends on what kind of index it is. If it is a non-clustered index SQL Server might as well do a table scan, and filter out the irrelevant rows. Have a look at the execution plan for this query. (if you use Management Studio, select the query, and press Ctrl-l, then look for "table scan")
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
As Andras pointed out, if it is a clustered, it will probably use it. But using a non clustered and * will most likely never use it.
The reason being, to find the row in the index and then having to do a book mark look up to find the corresponding row is deemed more expensive that a table scan.
Another point to look for in the query plan is the feature that SQL 2995 introduced to reduce book mark lookups.
Just for some clarification: If you have a non covered index being used in a query, to get around doing a book mark lookup, SQL does at times, open up a clustered index (if it is there) and join the two together. There are obviously many more requirements that have to be met before this will take place but I have seen it happen quite often. if you see this, it is also an indication of a poor index design.
My 10c worth
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
I am not really expert in SQL, but i did that, i get numbers:
Index seek cost
RID lookup cost
so I guess if these are high, then it' not good?
and of course if there is a table scan, then that means the index is not effective, right?
tx again!
-
Correct. Scans = bad. Seeks equal good.
A mention on the cost though: Everything has to add up to 100%. This means you need to get the best operations to have the hiest cost.
i.e. An index seek is 90% and a join 10% is better than the other way round. (This is an example, you would not normally have a cheap seek and an expensive join cost. Well, mostly :doze
Try get rid of the RID lookup. Look into covering indexes or use SQL 2005's INCLUDE feature for indexes.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Great help!
Tx!
-
>i know that using OR disregards the index (eg: select * from table where date = x or date = y ) , but what about > ?
This isn't correct.
You can use OR or any operator and still have indexes being used.
In the above example, it depends on how many rows that 'x or y' are judged by the optimizer to be returned.
Depending on this, the optimizer will either use the index, or perform a a scan.
Just using the OR operator does not automatically disregard an index.
(Nor does the '>' operator)
/Kenneth
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply