Adding Extra (refined) indexes to SQL Server table
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?
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 (ie none):
Sturgeon's law suggests it's probably not doing anything useful, but Murphy's law suggests removing it will break something.
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.
eg 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.
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.
链接地址: http://www.djcxy.com/p/59314.html