Using Index in Open Select statement

[indydavid32]

I have an SQL 2000 table that has a non clustered Index built. When using Select statements in VB to open this table, do I need to reference the Index that is built or is the Index automatically used? If not, what is the correct syntax for using the Index in my open statement?

Thanks

[WALDO]

I do believe the nidex is automagically used.

[indydavid32]

What if there are multiple indexs built? Does it automatically use the most efficient one?

[WALDO]

I believe so. If anyone would care to rebut, be my guest.

[RSINGH]

The wrong index can be used. In the case of multiple indexes, you can specify the one that you want to use as thus.

SELECT blah
FROM blah (INDEX = indexname)

or something like that.

[Leather]

I would rely on SQL server getting it right 99.10102099919% of the time tho...

[RSINGH]

True, but in my experience on VLDB's with several indexes on a table, the wrong one has been used. This is more likely on views with many tables and JOINS. Otherwise you're right.

nb - you're 0.004352001 out