Clustered index choice on a Replicated database

• mayurkb

  Ten Centuries

  Points: 1155

  More actions

  October 17, 2016 at 9:21 am

  #331040

  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. We are facing 2 issues right now.

  1. At some sites, we are running out of integer values on the int column even though the number of records are no where near 2 billion. This happens because of identity ranges that are assigned to each subscriber. Some use it and some don't and every time we do upgrades, new ranges are assigned leaving old ones unused.

  2. Sometimes, subscriber runs out of values from the identity range that it was assigned.

  I see couple of solutions for this:

  1. Use BigInt column instead of int and keep identity. This will give bigger ranges. And also overall bigger datatype to work with. I'm not sure if this solution resolves the problem forever and if the problem will not come back few years later.

  2. Get rid of identity column altogether 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've read all the Kimberly's blogs and all of them do kind off say bigint would be a better choice in general. But none of them had the replication scenario in them.

  Any suggestions or experiences on either of the approaches are highly appreciated.