I am working on a application that now requires off line clients to connect and sync up the data. I have a table that has an integer type identity column primary key.

I want the traditional/connected application to use values less than 9999999 and the clients node to use a unique range of ids that are assigned to each client always above 9999999.

The problem I have is when the client connects and inserts records with a primary key greater than 9999999 the next time the connected does an insert the identity value issued is above the 9999999 range and can conflict with a client generate id.

I could use DBCC CHECKIDENT to reset the seed value after an off line client syncs their data. My concern with this approach is that it may not run quick enough to ensure no conflicts occur.

newseed = select MAX(idnum) from consumer where idnum < 9999999
DBCC CHECKIDENT (consumer, RESEED, newseed)

Is there some way to force SQL Server to use the lowest available value for identity inserts or to stop the seed value from changing when I do an IDENTITY_INSERT? I would use GUIDs if this was a new app but as it stands now I am stuck with the integer primary key.