CODEWITHSUNDEEP
sql-serversql-server-2008identity-column
KB5
KB5

Reputation: 128

Generating unique identity column value across different SQL Server instances

I have WinForm application (C#) and Backed as SQL Server 2008, which works offline at client location. Currently all branches were getting managed from single location, so an identity column value is always unique across branches.

Now, I want this application to be managed from multiple locations and should work offline as well as online. To make it possible we are putting a SQL Server in PUBLIC IP and each branch will have a separate local SQL Server instance running. The data wil be synced between local and central sever on regular intervals.

Is this approach good to sync data, or is there something better that I can do?

If I go with above approach the problem that I will face is in syncing the data. There is a problem with table structure, eg. I have a table COURSES as follows:

COURSES ( COURSE_ID (identity column), COURSE_NAME, COURSE_BRANCH_ID)

where

  1. COURSE_ID is an IDENTITY column
  2. COURSE_NAME represents the name of Standard
  3. COURSE_BRANCH_ID represents the branch, where the course is taken.

Now each SQL Server will generate its own value for the COURSE_ID column and that might be same for different server.

  1. The unique combination is COURSE_ID and COURSE_BRANCH_ID.
  2. Is there any way I can a append COURSE_BRANCH_ID with COURSE_ID without adding a new IDENTITY column?

Approach I thought of

  1. Remove identity from COURSE_ID column,
  2. Add a new column say ID which will be a identity column.
  3. Now after insert on COURCES table write a trigger which will update the value of COURSE_ID as Concate(COURSE_BRANCH_ID,ID)
 convert(number(convert(varchar,COURSE_BRANCH_ID) + convert(varchar,ID))

But this will require lots of efforts as I have around 19 tables with such problem. I there any thing better than this we can do? Any suggestions are welcome! Thank You!

Upvotes: 0

Views: 234

Answers (1)

Ahmed Saeed
Ahmed Saeed

Reputation: 851

There are several approaches related to this issue. The one you mentioned where you concatenate the branch id to the identity field.

You can use GUID, the possibility of collision is almost zero. Or you can set the Identity Seed and Increment, such that each branch has a different start value, and all incremented by the number of branches.

For example, if you have four branches, then on Branch1 you may set the 

ID INT IDENTITY(1, 4) NOT NULL -- IDs will be 1, 5, 9...etc.

On Branch2 

ID INT IDENTITY(2, 4) NOT NULL -- IDs will be 2, 6, 10 ...etc

On Branch3 

ID INT IDENTITY(3, 4) NOT NULL -- IDs will be 3, 7, 11 ...etc

And on Branch4 

ID INT IDENTITY(4, 4) NOT NULL -- IDs will be 4, 8, 12 ...etc.

Upvotes: 2

Related Questions