1

I'm still learning the finer points of indexes and this is something that I cannot see a need for. Hopefully the community can point me in the right direction.

The table has 6+ fields.

Field1 is a uniqueidentifer and is the PK. Field2 and 3 are also uniqueidentifiers. The rest are varchar/ints and irrelevant as far as I can see.

three index have been placed on the table: Clustered PK Nonclustered Non-Unique on Field2 Nonclustered Non-Unique on Field2 AND Field3

There are no included columns on any of the indexes.

My question is, is there any reason to have the single index on field2? My understanding is that there should be no difference in the seeking of the index if there are two columns or one?

Improve this question
2
  • 1
    I'm guessing Field2 and Field3 are foreign keys?? Commented May 14, 2012 at 11:43
  • one is, one isn't (the fact that they're uniqueidentifiers is a rant for another time :)) Commented May 14, 2012 at 12:09

4 Answers 4

Reset to default
1

You are right. There are few reasons I can think for the index on Field2 alone being useful, given the existence of the index on Field2 and Field3 and the fact that the included columns are the same (i.e. none):

  1. To ensure Field2 is unique (if it is a unique index) - given that Field2 is a uniqueidentifier, this is highly unlikely
  2. Esoteric performance reasons (technically, the index on Field2 will be smaller so it will be less I/O burden).
  3. Esoteric locking reasons

Sturgeon's law suggests it's probably not doing anything useful, but Murphy's law suggests removing it will break something.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

The number of columns (data) increased in defining index, it means the index size will increase proportionally. So it's advised that Primary key (index) should be create on small/integer field.

e.g. imagine you search a table on three columns

state, county, zip.

you sometimes search by state only. you sometimes search by state and county. you frequently search by state, county, zip. Then an index with state, county, zip. will be used in all three of these searches.

If you search by zip alone quite a lot then the above index will not be used(by SQL Server anyway) as zip is the third part of that index and the query optimiser will not see that index as helpful.

You could then create an index on Zip alone that would be used in this instance.

I guess the answer you are looking for is that it depends on your where clauses of your frequently used queries and also your group by's.

Improve this answer

4 Comments

thanks for the answer, however the question is about the other 2 indexes, we need to use uniqueidentifers for other reasons that I won't go into.
Now look at modified post and you will find your answer to have a different index. In your case, you have index on Field2 + Field3, so it is not advised to have index on Field2. If you do execute queries with filter on field3 then create an index on Field3.
So to clarify, if there is a search just on Field3, no indexes will be used. if there is a search on Field2, the planner should choose the most recently used index. When a search is made on field2, there shouldn't really be any difference in the performance of using either of the 2 indexes?
The performance may be differ for field2 (using single and multi-column index) if hardware is limited. Otherwise you are correct.
0

Field1 has an index because it's it has been named the primary key and likely has a default value of newid(). It has to be unique.

The reason Field2 has an index is because it's a foreign key and will likely be found in many where clauses and inner join statements.

Not sure why Field3 received an index, but if it's used in any where clauses it's good to have it there.

Indexes are all about finding information fast. Examine all of your where clauses and determine the best indexes for your individual needs.

Improve this answer

Comments

0

My only concern would be if Field2 is an FK and a deletion is done in the table it refers to, whether the optimizer is smart enough to use the composite index with Field2 as the first column to check and ensure that nothing refers to the row being deleted. Of course, the wider index is still somewhat less efficient, because it fits less rows per page.

The only other thing might be ascending/descending issues, but you didn't mention a difference there.

You could check the execution plan for such an operation and the missing indexes DMV after removing the redundant index.

Normally we always start with PK, FK all indexed so basic integrity-related operations are ok for performance; then composite indexes are added for read performance. Obviously, at that point some of the FK indexes end up being redundant and you end up in this situation.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.