Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqldev6
Starting Member
18 Posts |
 Posted - 2008-02-13 : 06:07:26
|
| we have 2 columns like col1,col2 ina table.We have one composite clustered,Unique index on above two columns.Again some on has created non clustered indexes on above columns for each column.Is this will cause for any performance degradation?Thanks in advance. |
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2008-02-13 : 06:14:27
|
| If you're going to perform many deletes/inserts on the table then there will be extra cost as SQL Server has to manage both the clustered and non clustered indexes. But then again, it all depends on the purpose of the indexes. If you’re going to be regularly filtering on one column or the other then it's worth having separate non clustered indexes IMHO. |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2008-02-13 : 08:09:31
|
| If clustered index is on col1+col2, then a seperate index on col1 is of minimal (if no) use.The col2 index may be useful.There's a insert/update cost for an index....you have to work out if the benefit (for selects) is worth having it there. There is no absolute answer. |
|
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2008-02-13 : 08:31:42
|
quote:
If clustered index is on col1+col2, then a seperate index on col1 is of minimal (if no) use.
Could you expand on that a little please? |
|
|
|
sqldev6
Starting Member
18 Posts |
Posted - 2008-02-13 : 09:20:30
|
| I need some more information...We have one table with PRIMARY KEY NON CLUSTERED On one column(Col1).Again table has the CLUSTERED INDEX on the same column(Col1).Will this create any problems........Thanks in advance. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2008-02-14 : 07:15:01
|
| abu-dina..."Could you expand on that a little please?"...An index in a telephone book listing all the "murphy andrew", "murphy bob", "murphy dave" in the worlds will also be useful to find all the "murphy"s...if however you want to find all the "andrew"s then a different index would be of more use.sqldev6...one of those 2 indices is a waste of time and space. not sure if it will actually "cause" problems - but it won't help. |
|
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2008-02-15 : 07:10:19
|
quote:
Originally posted by AndrewMurphy
abu-dina..."Could you expand on that a little please?"...An index in a telephone book listing all the "murphy andrew", "murphy bob", "murphy dave" in the worlds will also be useful to find all the "murphy"s...if however you want to find all the "andrew"s then a different index would be of more use.sqldev6...one of those 2 indices is a waste of time and space. not sure if it will actually "cause" problems - but it won't help.
In other words a composite clustered index consisting of Col1+Col2 should be sufficient for any searches on Col1. So there is no need for a new non-clustered on Col1 as it is already sorted. An index on Col2 could however be useful as explained in your analogy.Hope I am making sense. |
|
|
|
|
|
|
Clustered indexes and Non clustered indexes.