Adding Extra (refined) indexes to SQL Server table

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.