locked
Global Auto Increment Value RRS feed

  • 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

    Tuesday, November 28, 2006 12:58 PM

All replies

  • 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.

    Tuesday, November 28, 2006 2:02 PM
  • 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.

    Tuesday, November 28, 2006 8:36 PM
  • 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.

    Wednesday, November 29, 2006 12:21 AM