February 14, 2006 at 2:52 pm
Hi,
Say I've got a table with a composite index on columns A, B, C, D, and E. Say I've also got a singlet index on column B, and another on column C. Are the singlet indexes redundant and unnecessary? Is there a definitive answer to that question? Any links providing details on this would be appreciated. Thanks,
-E
February 14, 2006 at 3:16 pm
>>Are the singlet indexes redundant and unnecessary? Is there a definitive answer to that question?
There is no definitive answer, because it depends on the distribution of data in the table/indexes, on the type of queries that are executed and on the index properties (unique, clustered etc).
For example, if B is highly selective, and a query is executed that filters on B, but not on A, then the index on single column B might prove to be very useful.
February 15, 2006 at 5:57 am
In considering an index choice the optimiser will not use a composite index unless the first column of the index occurs within the query. So the short answer is your composite index does not replace single indexes by default. I've also found that single indexes even if ignored in a plan can still reduce i/o and improve performance for a query.
I'd be very wary of removing indexes unless you're sure. If access is all through procs then search syscomments for the table and column name to see if it is used as a search/insert /update etc.
If you have ad-hoc sql use profiler to capture sql to the table
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply