0

I recently started working on an application that has merge replication for offline scenarios. We have an int identity column that serves as the pk and clustering index. We also have uniqueidentifier column in each table because replication needs it. In the field, because of identity column, we sometimes face issue that the identity range runs out for 1 or more subscription databases. Then our support gets involved and spends lot of time compacting that tables, re-initializing subscription etc. To get rid of this problem, I see 2 solutions. 1. Use BigInt column instead of int and keep identity. This will give bigger ranges and hopefully we’ll never see that issue again. 2. Get rid of identity column and make the uniqueidentifier column serve as pk and clustering index. This column is already populated with newseqeuntialID(). This will permanently get rid of the problem. This will also reduce the overall size of the table since 1 column is gone from the table but it will increase size of the foreign key indexes and other non-clustered indexes.

I did some load testing and although bigint came little ahead in terms of timings, there wasn’t a huge difference.

What do you think about these 2 options or any other option that I didn’t think of?

May
  • 100
  • 1
  • 9
  • 2
    Have you looked at this https://blogs.msdn.microsoft.com/sqlserverfaq/2010/05/27/guid-vs-int-debate/ – Fran Sep 09 '16 at 17:49
  • Í vote for option 2 :-) – ErikEJ Sep 09 '16 at 17:49
  • 1
    Certainly don't use a guid as your clustered index. If you populate it with newseqentialid you will avoid fragmentation issues but you negated the only real benefit from using a guid in the first place and brought about all the pain of working with them. They take more storage, they totally suck to work with when debugging, etc...and the benefit of being a unique value is lost because they are predictable again. If you use BigInt you shouldn't ever run out of numbers. BigInt is HUGE. If you get 1 billion new values every day it will take 9 billion days before you run out of positive numbers. – Sean Lange Sep 09 '16 at 18:45
  • Thanks a lot guys. I was leaning towards biginto too but just wanted to get somebody else's opinion too. – May Sep 12 '16 at 15:27
  • I'm still skeptical about using bigint because there are still identity range management issues because of SQL replication. – May Sep 13 '16 at 20:09

0 Answers0