Unanswered: Should the INDEX name be used in SELECT statement
I am new to SQL, especially use SQL in VC++ 6.0 framework.
I am told that creating INDEX on field(s) could speed up a query.
if I create a INDEX like the following
CREATE nonclustered INDEX IX_XYZ on TableA.field1
Should I use the INDEX name IX_XYZ in some way in the following SELECT statement. Or the following SELECT statement will be carried out automatically based on the INDEX IX_XYZ.
SELECT * FROM TableA WHERE field1 = xxx
The query path optimiser decides for you which indexes to use (unless you use hinting) so you DON't need to specify which indexes you want your SQL query to utilise because MSSQL will work it out for you
So long as you have the index on the right column you can almost be safe in the knowledge that it's going to run your query better.
As aschk mentions, the query optimizer is very capable of determining which (if any) index(s) to use. Unless you are very VERY sure you know better than the optimizer which index to use, it's best to let it be.