Global Auto Increment Value

Question

Hi ,

In Sybase, there is concept of global autoincrement

The range of default values for a particular database is pn + 1 to p(n + 1), where p is the partition size and n is the value of the public option GLOBAL_DATABASE_ID. For example, if the partition size is 1000 and GLOBAL_DATABASE_ID is set to 3, then the range is from 3001 to 4000

If synchornization is done betrween 2 database . then in server , the new record will be created
The value of increment value would be max value at server +1

For Example
Database id of database(1) is 1

Table A has 3 rows
The values are 1000000001
         1000000002 
         1000000003

This database id of this database(2) is 2
The records in table A in this database 2000000001
    2000000002

Then synchronization is performed.
 The records from database(2) comes to Database(1)

So the records in Database(1) are

1000000001
1000000002 
1000000003
2000000001
2000000002

In sybase , when we insert a new record in Database(1), the new record will be 1000000004

I have transferred this type of sybase database(1)  to Sql Server using DTS.

When I create a new record on Sql Server , the new record which is getting inserted is 2000000003. I want the new record to be inserted is 1000000004.

How is it possible..

Thanks in advance

Answer 1

MS SQL does not have any way to do this built in.  IDENTITY columns are always unique within the table, not the database.

I have simulated what you are talking about by creating a seperate table with just one field of  the global increment number and using that.

Answer 2

Create a DatabaseID column in each table with the tinyint data type and create a default constraint for it with the database number for the corresponding database. Create an identity column. Make the primary key a compound of the DatabaseID and the identity column.

SQL Server's approach to uniqueness across replicated databases is to use the uniqueidentifier data type with a default value based on calling newid(). If you care about the database that owns the row you'll have to have some kind of DatabaseID column then too though.

Answer 3

There is no built-in functionality available to recycle old identity values or gaps. You will have to do that yourself. The link below has a solution that can be used to find gaps in sequential numbers and you can use that to get the next id:

http://www.umachandar.com/technical/SQL6x70Scripts/Main67.htm

Note that you will have to perform the appropriate locking depending on your concurrency requirements to get the correct results.