question

hardik.vaishnav avatar image
hardik.vaishnav asked

is it possible to create multiple non clustered index on single column

Hello All, i have a table with 5 fields like Field1,Field2... Field5. is it possible to assign multiple non clustered index on column Field2? if possible then how can we assign it ? i want to assigne multiple non clustered index on single column. Thanks
indexingnonclustered-index
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
It is certainly possible. You can use GUI as well as TSQL [CREATE INDEX][1] command. But why would one make multiple indexes on the same column is unimaginable. This redundancy could make the performance dismal instead of optimal. [1]: http://msdn.microsoft.com/en-us/library/ms188783.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
It is entirely possible to do what you want to, but the question is really "Why?". Each index that you have on a table has to be maintained. Any update on the table that involves the columns of an index has to be maintained in the table and any corresponding indexes - this can *SERIOUSLY* slow down inserts, updates and deletes. If you created two identical indexes on Field5, then one of those indexes is basically a waste of space as you are storing (and maintaining) the same information twice. The only time I would create *similar* indexes is if you have queries that use a subsset of similar columns, but extra columns that make less sense to keep in one index. I would suggest you take a look at the queries you have and the table structure and find as few indexes as possible to cover your queries. A composite (or covering) index that is wider (has more columns) than required for one query, but through the widening covers 2 other queries is probably better than 3 separate indexes. If you need guidance on indexing, I would recommend asking a new question here with the DDL for the table(s) and index(es) and the queries you are wanting to tune. We can then suggest the best course of action.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yeah, but if the columns are in a different order I'd consider that a different index. Just changing which column is on the leading edge of the index rearranges the stats, so that's completely different. But I can't imagine having the same index, same leading edge, no other differences.
1 Like 1 ·
It can make sense under certain circumstances to have multiple indexes on the same column if those indexes are multicolumn and have the columns in a different order. But as a general rule, I agree that its not worth doing.
0 Likes 0 ·
No, I don't see a good case for two indexes with the same leading edge offhand either. It just seemed worth clarifying that it could make sense to have two indexes that shared a column as part of the index key, as long as there were other difference.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
The only reason I can think of for intentionally creating multiple indexes on a single column is because you intend to use filtering with these indexes and you're going to apply different filters to each of the different indexes. In that case, I can see a reason for multiple nonclustered indexes on the same column.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.