I have a table in SQL Server 2005 and I have an IDENTITY(1,1) property set for an ID column. Records are inserted in a table at a local server as well as a remote server. I am using Merge Replication to match the data from local server and remote server.

What happens if I insert the same ID at the local server and the remote server?

Please suggest what am I doing wrong here.


3 Answers 3


When using Merge with identity columns — will need to specify an identity range management option of either automatic or manual so that duplicates do not occur.

With automatic, replication will automatically manage the assignment of identity ranges for nodes participating in the Merge topology.

With manual, you will need to manually assign "islands" of ranges for nodes using DBCC CHECKIDENT.

Have a look at Replicate Identity Columns.


You should use a uniqueidentifer - a GUID - instead of an integer identity. Then both databases will have uniquely identifiable row ID that is guaranteed to be unique.

See http://technet.microsoft.com/en-us/library/ms151206(v=sql.90).aspx

  • I am creating billing system so biil can be made Locally(offline) or through a remote location (online) and want to generate a bill number which is meaning full
    – Rakesh Sawant
    Commented Oct 26, 2012 at 11:45
  • Well you'll have to find some other way of distinguishing them. That doesn't affect the fact you're going to have to have a GUID.
    – podiluska
    Commented Oct 26, 2012 at 11:48

When you state:

What happens if I insert the same ID at the local server and the remote server?

I assume you are doing:

INSERT INTO dbo.[TableName] (ID, ...)
VALUES ([x], ....)

The short answer: Do not explicitly insert ID's with a specific value into a table with MERGE replication in place, as @Brandon Williams stated, you should be letting Merge replication handle the IDENTITY ranges for each DB in your MERGE topology.

From MSDN:Replicating Identity Columns (emphasis added by me)

When you assign an IDENTITY property to a column, Microsoft SQL Server automatically generates sequential numbers for new rows inserted in the table containing the identity column. For more information, see IDENTITY (Property) (Transact-SQL). Because identity columns might be included as a part of the primary key, it is important to avoid duplicate values in the identity columns. To use identity columns in a replication topology that has updates at more than one node, each node in the replication topology must use a different range of identity values, so that duplicates do not occur.

For example, the Publisher could be assigned the range 1-100, Subscriber A the range 101-200, and Subscriber B the range 201-300. If a row is inserted at the Publisher and the identity value is, for example, 65, that value is replicated to each Subscriber. When replication inserts data at each Subscriber, it does not increment the identity column value in the Subscriber table; instead, the literal value 65 is inserted. Only user inserts, but not replication agent inserts cause the identity column value to be incremented.

Note: Automatic range management uses thresholds to identify when should the DB ask for it's next range of ID's. ie: For a high throughput table you would want a large range size and a low threshold so that:

  1. @identity_range You don't need to keep requesting a new range of ID's every few seconds
  2. @threshold You allow enough time for the subscriber to negotiate the next range with the publisher and do not risk clients receiving errors as unable to write records

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.