locked
Create universal unique numerical value between multiple databases in SQL SERVER 2014 RRS feed

  • Question

  • Hi everyone,

    We have SQL SERVER 2014 environment. We have dedicated order table associated with different regions, and in each region the data is being inserted independently.In this environment we want to create a unique orderId between different region. This Id consists of two parts: region# + sequential number. We want to save as one value.

    Currently we have implemented through sequence object. Is there any other way to implement this?

    Thanks


    A.G

    Tuesday, January 17, 2017 7:56 PM

Answers

  • Would a computed column work for you?

    create table testcomputedcolumn(PK int identity , servername sysname default @@Servername, computedColumn  as 
    
     convert(varchar(100),pk) + ' ' + servername, charcol char(20))
    
     declare @counter int=1
     while @counter<100
     begin
     insert into testcomputedcolumn(charcol) values(replicate('x',20))
     select @counter=@counter+1
     end
     select * from testcomputedcolumn

    Tuesday, January 17, 2017 8:06 PM
  • Hi A.G.

    Database designing is approach based. Here's my perspective from what I read from your question.

    You can create a new table [REGIONS] (assuming you need flexibility managing the regions going forward) and have columns [RegionId] (PK), [RegionName], ..etc

    Then add a foreign key constraint to all the [ORDERS] tables as [RegionID] (FK) for all 'Region wise Orders tables'. (Assuming you have multiple ORDER tables from your statement "We have dedicated order table associated with different regions, and in each region the data is being inserted independently.")

    1. One way is to store the UniqueOrderId 'region#+sequentialnumber' is in the [ORDERS] tables in a new columns. (not my preferred way)

    2. Other way is not to store the UniqueOrderId 'region#+sequentialnumber' in the physical table but you can create a VIEW to display the UniqueOrderId on the application front-end and reports.

    Cheers,

    Sunit



    Tuesday, January 17, 2017 8:23 PM
  • Normally you do not do what you describe.

    You would have a "Region" and a "Order Number" for that Region (possibly starting at 1 for all regions).  Then when you combine regions for reporting purposes, you append them together or just display both fields.

    Tuesday, January 17, 2017 9:01 PM
    Answerer

All replies

  • Would a computed column work for you?

    create table testcomputedcolumn(PK int identity , servername sysname default @@Servername, computedColumn  as 
    
     convert(varchar(100),pk) + ' ' + servername, charcol char(20))
    
     declare @counter int=1
     while @counter<100
     begin
     insert into testcomputedcolumn(charcol) values(replicate('x',20))
     select @counter=@counter+1
     end
     select * from testcomputedcolumn

    Tuesday, January 17, 2017 8:06 PM
  • For multiple database replace servername with dbname and the default of @@ServerName with db_name().
    Tuesday, January 17, 2017 8:08 PM
  • Hi A.G.

    Database designing is approach based. Here's my perspective from what I read from your question.

    You can create a new table [REGIONS] (assuming you need flexibility managing the regions going forward) and have columns [RegionId] (PK), [RegionName], ..etc

    Then add a foreign key constraint to all the [ORDERS] tables as [RegionID] (FK) for all 'Region wise Orders tables'. (Assuming you have multiple ORDER tables from your statement "We have dedicated order table associated with different regions, and in each region the data is being inserted independently.")

    1. One way is to store the UniqueOrderId 'region#+sequentialnumber' is in the [ORDERS] tables in a new columns. (not my preferred way)

    2. Other way is not to store the UniqueOrderId 'region#+sequentialnumber' in the physical table but you can create a VIEW to display the UniqueOrderId on the application front-end and reports.

    Cheers,

    Sunit



    Tuesday, January 17, 2017 8:23 PM
  • Normally you do not do what you describe.

    You would have a "Region" and a "Order Number" for that Region (possibly starting at 1 for all regions).  Then when you combine regions for reporting purposes, you append them together or just display both fields.

    Tuesday, January 17, 2017 9:01 PM
    Answerer