Index and Order by CLAUSE 1

[popseven]

Hi Guys,

If I have a mssql 2000 table named CLAIM wherein I have created a non-clustered index named CLAIMDATE_IDX (for the field CLAIM.CLAIMDATE). If I issue a query like:

select * from claim
where claimdate = '01/20/2006'
order by claimdate

Will mssql automatically use the CLAIMDATE_IDX since the CLAIMDATE field is specified on the ORDER BY clause?

TIA,
popseven

 

[Gixonita]

Hello,

The index is used independently of your order by clause, the engine will try to seek for the best path to obtain your records and since an index exists on claimdate then it will be used.

The order by clause if more for "output presentation" purposes (and in the example that you show is redundant since you are asking for a specific date so basically there is nothing to order).

Hope this helps.

 

[SQLSister]

It will not automatically be used. Check the execution plan to see whether it is being used. Specifying only the fields you need can change the way the indexes are chosen and it is a better practice than using select * most of the time.

Gixonita, there is something to order on because date fields include the date and time.

Questions about posting. See faq183-874

 

[Catadmin]

If you want to specifically use the index (and there are times where you will not because of performance issues), you can use Index Hints.

Look up the keywords "With" and "Index Hints" in Books Online for more details on this matter. Just remember, Index Hints are neat and wonderful for some queries, but other times, you can kill your query time by playing with them. Always check the estimated execution plan to verify whether or not using them causes you issues.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[gmmastros]

In case you didn't know...

You can view the actual execution plan when you run your query in Query Analyzer by pressing CTRL-K and then running your query. Near the bottom of the window, you will see a tab for execution plan. Hovering your mouse over the icon will display information regarding that step in the execution plan.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Gixonita]

SQLSister,

I have never encountered a situation where my index is not used when I include the indexed field in the where clause, specially in a simple query as the one provided (unless there is so little data that a sequential scan turns out faster, in which case I wouldn't create an index in the first place).

In the order clause I assumed that the time part is not used since no reference to time was made (here we have lots of datetime fields where the time part is never used), that's why I found the order by redundant.

Thanks for your comments

 

[fredericofonseca]

Gixonita,

If you have a table with more than one index, clustered by index A, and where index B has a very cluster ratio, then depending on the query ("where" clause mainly, but also columns selected and size of table (number of records mainly) the engine can decide to do a table scan instead, or even use another index if any of the where clauses are part of that index.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com