# SQL Server Rule: SRP0017
| | |
|----|----|
| Assembly | SqlServer.Rules |
| Namespace | SqlServer.Rules.Performance |
| Class | AvoidUpdatingPrimaryKeyColumnsRule |
## Rule Information
| | |
|----|----|
| Id | SRP0017 |
| Friendly Name | Update of Primary key |
| Category | Performance |
| Ignorable | true |
| Applicable Types | Procedure |
| | Scalar Function |
| | Table Valued Function |
## Description
Avoid updating columns that are part of the primary key. (Halloween Protection)
## Summary
Avoid updating columns that are part of the primary key. (Halloween Protection)
### Remarks
<list type="bullet">
<item> Halloween protection will be invoked causing the data being updated to be done in
a two step operation that spools to tempdb. </item>
<item> All the foreign keys that reference the updated key have to be also updated. If
the foreign keys are indexed, it will cause their indexes to be also updated, which
can be an expensive operation. Otherwise, if no index exists for foreign key
columns, a table lock will be applied. </item>
<item> When the table has Change Tracking enabled, the values of the primary key column
identify the rows that have been changed and this is the only information from the
tracked table that is recorded with the change information. If the synchronization
of the changed data is implemented based on the Change Tracking, it will fail
because of the modified primary key column values. </item>
<item> If this key is referenced in any external system the reference will be broken
upon update. </item>
<item> The primary keys are usually clustered. Updating the table's clustered index
will cause also update of the existing non-clustered indexes. </item>
</list>
<sub><sup>Generated by a tool</sup></sub>
