Cluster index

If I changed my unique cluster index to non-unique cluster index, what impact
this can cause? please help !
Thanks
You can have non unique values.
Im guessing you meant to ask more than that?
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> If I changed my unique cluster index to non-unique cluster index, what
> impact
> this can cause? please help !
> Thanks
|||"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> If I changed my unique cluster index to non-unique cluster index, what
> impact
> this can cause? please help !
> Thanks
It depends...
It will allow you to add duplicate (index column(s)) to your table.
All non-clustered indexes on the table will be rebuilt.
Internally, SQL Server tracks the duplicate cluster key columns.
Rick Sawtell
MCT, MCSD, MCDBA
|||Sorry for not specific.
How much it can affect the perfomance?
Thanks.
"ChrisR" wrote:

> You can have non unique values.
> Im guessing you meant to ask more than that?
>
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
>
>
|||If we are running reports off this table, how slow the performance can be?
Thanks for your help.
"Rick Sawtell" wrote:

> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> It depends...
> It will allow you to add duplicate (index column(s)) to your table.
> All non-clustered indexes on the table will be rebuilt.
> Internally, SQL Server tracks the duplicate cluster key columns.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||Are you talking about this scenario:
You currently have a unique clustered index on column A. You want to change that to a non-unique
clustered index on column A. If so, it is a very odd viewpoint. Why would you want to do that? Have
you discovered that you suddenly want to allow duplicates in column A?
If you provide more information, we can be more specific.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:2B25D1BD-989A-413D-B9F6-C7290AAB22FB@.microsoft.com...[vbcol=seagreen]
> Sorry for not specific.
> How much it can affect the perfomance?
> Thanks.
> "ChrisR" wrote:
|||"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:54022448-21BF-472D-88E0-105EEEC362C9@.microsoft.com...
> If we are running reports off this table, how slow the performance can be?
> Thanks for your help.
> "Rick Sawtell" wrote:
How many more records are there going to be because of duplicates? That
will be the difference.
Rick
|||Thanks for pointing out.
Yes, since we are going to have duplicate values in one of the cluster index
columns. now we have to change the index from unique cluster index to non
unique cluster index. Basicly we are running reports off this table, I would
like to hear your idea on this kind situation, you may have better idea to
keep or speed up performance.
Thanks a lot.
"Tibor Karaszi" wrote:

> Are you talking about this scenario:
> You currently have a unique clustered index on column A. You want to change that to a non-unique
> clustered index on column A. If so, it is a very odd viewpoint. Why would you want to do that? Have
> you discovered that you suddenly want to allow duplicates in column A?
> If you provide more information, we can be more specific.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:2B25D1BD-989A-413D-B9F6-C7290AAB22FB@.microsoft.com...
>
|||I am exactly not sure , thousands may be.
Thanks
"Rick Sawtell" wrote:

> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:54022448-21BF-472D-88E0-105EEEC362C9@.microsoft.com...
>
> How many more records are there going to be because of duplicates? That
> will be the difference.
>
> Rick
>
>
|||Ok, sounds like a strange requirement to me, but you know your business better than I do :-)
The optimizer can sometimes pick a better plan from the sheer fact that it know that there can be no
duplicates in a column. I remember a case where the optimizer didn't want to do a merge join until I
added a unique index on one of the joins columns. That speeded up the query very much.
Sometimes, the optimizer also can pick a plan faster from the same fact.
Since it is a clustered index, you don't have to worry as much about the otherwise obvious cases.
Like returning one row vs. 10000 rows based on a search condition, where 10000 rows would result in
10000 page accesses (which is essentially how non-clustered indexes work).
Bur don't ask us to quantify this. It is impossible. You have the data, the indexes and the queries.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:DEBC853C-181F-42A2-89B1-A4F2C29EF183@.microsoft.com...[vbcol=seagreen]
> Thanks for pointing out.
> Yes, since we are going to have duplicate values in one of the cluster index
> columns. now we have to change the index from unique cluster index to non
> unique cluster index. Basicly we are running reports off this table, I would
> like to hear your idea on this kind situation, you may have better idea to
> keep or speed up performance.
> Thanks a lot.
> "Tibor Karaszi" wrote:
sqlsql