I am working on a db optimization (planing for future project growth) and need some help.
Currently, every table is using uniqueidentifier as PK (clustered index) and we have high index fragmentation (99%). For the new tables we started using bigint as pk but I don't want a nightmare when bidirectional replication phase comes.
I did my research and uniqueidentifier is not a huge problem (except memory), problem is clusters index on that column (http://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/).
Can we do this to solve our fragmentation problem and any replication nightmare:
- Add ROWGUIDCOL to PK column
- Add another identity column in every table and move that cluster index (not pk) to that column
Would this new identity column cause the same replication problems as if that was bigint PK? I know SharePoint db primary keys are GUIDs but I am not happy with its performances and probably security has some part in that decision. We could reduce fragmentation by using seq. GUIDs but we can't create it on application side or return it with SCOPE_IDENTITY(). OUTPUT inserted.ID hack would be time consuming because we need to rewrite complete application DAL.
Finally, is there any valid solution for this problem? Can we use bigint without any replication problem.