February 8, 2016 at 7:11 am
I have SQL instances deployed in 3 data centers with increment ID columns. In each SQL instance, these columns must remain unique.
Is there a native way in SQL to keep unique or will i have to generate programmatically?
I know there in mySQL there is auto_increment_increment and auto_increment_offset.
Any information you can provide is appreciated.
February 8, 2016 at 7:25 am
cheshirefox (2/8/2016)
I have SQL instances deployed in 3 data centers with increment ID columns. In each SQL instance, these columns must remain unique.Is there a native way in SQL to keep unique or will i have to generate programmatically?
I know there in mySQL there is auto_increment_increment and auto_increment_offset.
Any information you can provide is appreciated.
By 'increment ID', I presume you mean Identity() columns?
You could implement this through appropriate use of SEQUENCEs.
Or you could switch to using GUIDs rather than identities to be assured of uniqueness.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 8, 2016 at 7:45 am
Basically what you're describing is the underlying tables of a "distributed partitioned view" design pattern.
https://technet.microsoft.com/en-us/library/ms188299(v=sql.105).aspx
What you'll need to do is have each table insert identity values within a unique range. You do this by seeding each table's identity column to start at the beginning of it's domain and increment by 1. Check constraints can by used to enforce each table's domain. In the example below, we're allocating each table a range of 1 million IDs. Depending on your needs, you can allocate 10 or 100 million.
StoreA:
create table Orders
(
OrderID int not null identity( 1000000, 1 ) primary key
check constraint cc_OrderID (OrderID between 1000000 and 1999999)
, ...
);
StoreB:
create table Orders
(
OrderID int not null identity( 2000000, 1 ) primary key
check constraint cc_OrderID (OrderID between 2000000 and 2999999)
, ...
);
This is optional, but following the distributed paritioned view pattern, we also create a view that unionizes all distributed tables.
create view dvOrders
as
select OrderID, ... from StoreA.Sales.dbo.Orders
where OrderID between 1000000 and 1999999
union all
select OrderID, ... from StoreBSales.dbo.Orders
where OrderID between 2000000 and 2999999
GO
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 8, 2016 at 8:12 am
Yes, Identity columns().
Can't switch to GUID's... Too many tables and too much code.
For sequences - would need to define with specific data center indicator.
February 8, 2016 at 12:03 pm
The simplest solution, if you are sure that the number of instances will always be three, is to use identity with a starting value of 1, 2, or 3, and an increment of 3. That generates ranges that will never overlap. (You can also pre-assign ranges like 1-100000, 100001-200000, etc - but those tend to run out)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply