Recently I had the chance to take a look at a problematic query in an application. I caught the query in profiler and it’s the following:
The query was throwing the following error:
The query processor ran out of stack space during query optimization. Please simplify the query.
The error message is reported on connect, and the same is closed by design reasons. By database design, it’s a limitation for the number of foreign keys to 253.
Analysis of the code and values of @Id and @OriginalId variables showed that they have the same value. Additionally the PK occurs as a Foreign Key (FK) in 321 tables across the database.
I asked the developer if there is really a reason to update the primary key [Id]. After confirming this, we removed the [Id] = @Id in the SET part of the query and the problem disappeared.
Conclusion
Primary keys should not be updated. If it’s referenced in many tables, then its update withdraws updates for potentially many tables. Update in many tables could introduce significant fragmentation in the database. The update could happen to introduce data inconsistency so that the query will fail for other reasons. Even though PKs can be updated, they are not recommended to. Many other issues can occur in replicated databases which is another reason for not updating (possibly changing) the primary key.
