Guru's Guide to SQL Server Architecture and Internals, The

Identity Range Management

One of the common problems you often face with typical replication scenarios is managing identity ranges on the publisher and subscriber effectively. If multiple parties can insert new rows, how do we keep their identity values from colliding?

There are several solutions to this problem. One that I've used in the past is to set up odd-even/negative-positive identity values. For example, say that you have a simple implementation with a publisher and a single subscriber. You could seed the publisher's identity value at 1 and increment it by 1 with each row insertion, and you could set the subscriber's seed value to –1 (remember, SQL Server's int data type is signed) and increment it by –1 with each new row. Now let's say ...