How to generate unique integer IDs across multiple systems

Problem:

Let's say that you have a project that spans across many environments, as they usually do. For example you have a project in development, staging, and production environments. Part of the functionality of this project is to house content, doesn't really matter which type of content or where (files or database). What does matter is that you cannot use GUID to uniquely identify it. In fact the only thing that you can use is integers. Generally repeating IDs in different environments are not a problem, but once in a while you need to migrate something from dev to staging, or from staging to production.

Solution:

For the sake of being generic and accommodating people who have more than 2 environments, let's take the number of environments to be N (where N>2, because let's face it, if you have 1 environment, you don't really have a problem, and if you only have 2 environments then you can go with odd numbers in one, and even numbers in another).

So how do we deal with N environments?

Pre-requisites: We need to be able to tell each environment how many environments are there in total, and what environment are you in currently. If you are dealing with a web based application there are 2 places where you can put that. If the ID is being generated on the application level, you should store these 2 values in web.config file. If you are generating the IDs on the database level, I suggest storing these values somewhere in the master database, because in case you want to restore your production database backup in staging, the last thing you want is for the identity of your environment to be overwritten.

Now that we have these 2 values securely stored we can proceed to the algorithm of ID generation.

The math/algorithm of this is rather simple:

1. Find the largest ID that you have in the system
2. Keep increasing it by one.
3. Divide the number from step 2 by the Total number of environments. Check the remainder from the division
4. Stop increasing the ID when the remainder received in step 3 is equal to the environment you are in now.

Usually the data is being stored in the database and the IDs come from the database as well upon addition on the new content, so for the database, the call and the function would look like this:

--call
Select @Content_ID=CASE WHEN max(Content_ID) is NULL THEN 0 ELSE max(Content_ID) END
From tblContent
Select @Content_ID=dbo.fn_GenerateEnvironmentSpecificID(@Content_ID)
--Fuction
--*****************************************************************************
--* fn_GenerateEnvironmentSpecificID
--*****************************************************************************
ALTER function fn_GenerateEnvironmentSpecificID (@Item_ID int)
RETURNS int
AS
BEGIN
DECLARE @EnvironmentRemainderForID int, @TotalEnvironments int,@CurRemainder int
Select @EnvironmentRemainderForID=convert(int,Value) From master..tblStaticValues Where Name='EnvironmentRemainderForID'
Select @TotalEnvironments=convert(int,Value) From tblConfig Where Name='TotalEnvironments'
IF @EnvironmentRemainderForID is NULL OR @TotalEnvironments is NULL
RETURN -1
Select @CurRemainder = -1
WHILE @CurRemainder <> @EnvironmentRemainderForID
BEGIN
Select @Item_ID = @Item_ID + 1
Select @CurRemainder = @Item_ID % @TotalEnvironments
END
IF @Item_ID is NULL
RETURN -2
RETURN @Item_ID

I would also like to thank Brian Kroski for the "even and odd numbers" idea.

SQL Code Contribution by Albert Gorbatenko.