What is the impact of changing a Unique key in SQL Server 2005
I am having a table one primary key ID int and composite unique key for 4 fields. But due to nature of my project one of the keys(fields) of the composite key keeps on changing.
Does anyone find any problem in changing the field of composite key that often?
The index will need some reorganisation.
This is part of the C in ACID: When your UPDATE completes everything is done and dusted.
Also, any indexed views using the data will need updated too, again part of the "C".
If it's not clustered then this is about it.
I wouldn't worry about it too much unless it's happening many time a second...
I would just be sure to add some code to be watchful of unique constraint violations. You shouldn't run into a problem, but if you're changing it that often, I would say that you run a greater risk.
there is maintenance involved since all nonclustered keys point to either the clustered key or to the row if you have a heap (table without a clustered key)
Since the clustered key holds all the data for the table (in essence it is the table) whenever you make changes to the nonclustered key the clustered key will be updated and vice versa