Thursday, August 25, 2016

Performance issues with Composite Primary keys Index fragmentation

Say you have a table with 10 columns and 3 columns (id, name, phone) as composite keys
when you have lot of updates on Name, Phone ...the index on composite key get fragmented a lot.
better
try to keep ID Primary key and have clustered index
or
keep id name then have composite key

and think of option whether to keep a constraint on Phone

Note:
you should first see how the index is getting fragmented and do some analysis , test thoroughly so that application won't break and then implement in Prod

Thanks 

No comments:

Post a Comment

https://blog.sqlauthority.com/2009/06/27/sql-server-fix-error-17892-logon-failed-for-login-due-to-trigger-execution-changed-database-context...