(This began as a VB forum post but I think it's better suited for this forum....)


Winan was asking if he could use the ADO index and seek functionality with a SQL Server provider, and I responded "no" and suggested that he might be able to rewrite some of the queries to let SQL Server narrow down the data a bit more.... Here's what followed:

>Thanks for your quick reply, you mean that I have to change the >open "Table" statement to open "Select * from blahblah where blah blah >= ..." ?

Yes, that is what I mean.

>if "Yes".. my question is how about the indexes that I created ? are they >automatically use by the system regarding my "Order by .." statement .. ? >or if "Not" how to use it .. ?
>

Well, it's pretty much automatic for the selection criteria and the join. (That's a bit oversimplified -- there are certain expressions and approaches which will not make use of an index, and the best thing to do is run the query in Query Analyzer with "Show Execution Plan" on and see that it's using the index.)

I'll be honest -- I am not sure if the ORDER BY part actually uses an index. I just did a quick test and it seems that if I order on the clustered index no additional sorting is necessary (this is no a surpise). But, if I order on a non-clustered index, the execution plan looks identical (and the cost of the ordering identical) to a case where I order on a non-indexed field.