mfarid1
asked on
Using indexes
I have a table with an index whose name is _WA_Sys_MVndNbr_12A9974E. When I try to drop the index, I get the message that the index does not exist in system catalog. However, the index does exist in sysindexes.
Failing to drop the first index, I created a second index on the same column called Test_MVndNbr.
My questions are:
1)How do I drop the index _WA_Sys_MVndNbr_12A9974E?
2)How do I use my second index Test_MVndNbr? If I write a query which references the column on which Test_MVndNbr was created, is Test_MVndNbr used automatically by SQL?
Thanks.
Musleh
Failing to drop the first index, I created a second index on the same column called Test_MVndNbr.
My questions are:
1)How do I drop the index _WA_Sys_MVndNbr_12A9974E?
2)How do I use my second index Test_MVndNbr? If I write a query which references the column on which Test_MVndNbr was created, is Test_MVndNbr used automatically by SQL?
Thanks.
Musleh
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
By default, SQL Server chooses which index to use.
Query optimizer (the component responsible for generating the optimum execution plan for a query) can use, if your query references an indexed column, the index related to it. (Remember Query optimizer chooses the most effective index in the majority of cases).
Note that if 'INDEX' table hint is specified, it forces SQL Server to use the index specified.
Query optimizer (the component responsible for generating the optimum execution plan for a query) can use, if your query references an indexed column, the index related to it. (Remember Query optimizer chooses the most effective index in the majority of cases).
Note that if 'INDEX' table hint is specified, it forces SQL Server to use the index specified.
ASKER
gpbuenrostro, thanks for your answer. Can you explain what you meant by,
"Note that if 'INDEX' table hint is specified, it forces SQL Server to use the index specified."
"Note that if 'INDEX' table hint is specified, it forces SQL Server to use the index specified."
You can instruct SQL Server to use a specific index. This can be done using‘table hints’ facility. For more information, see “Hints” topic in BOL.
to remove the index use
DROP STATISTICS [TABLE NAME].[_WA_Sys_MVndNbr_12A
DROP STATISTICS [TABLE NAME].[_WA_Sys_MVndNbr_12A
ASKER
2)How do I use my second index Test_MVndNbr? If I write a query which references the column on which Test_MVndNbr was created, is Test_MVndNbr used automatically by SQL?
What I mean to say is, do I have to say use this new index Test_MVndNbr before I execute a query? I don't see any performance gain after creating the new index.