Geek Answers Handbook

Auto add odd and even for two databases for synchronization without affecting the automatic growth property

Need quick help. I have my database with autointre bigint activity. I have a database in two places that need to be synchronized. Since bigint is not a good choice for synchronization due to the possibility of a primary key replica on different sites. I can’t move using the GUID, because for this I need to change my code, as well as the database, which is impossible for me.

Right now I have two places only for the database, so I think that if my primary automatic growth will always be even in one place and will be odd in another place. He can quickly solve my problem.

How can I do this using a computed column specification or in any other way. For synchronization I use Microsoft sycn framework.

If I use the server identifier (1,2) server or identifier (2,2) B after synchronization, it calls the following increment value. For example, if on server A max id 3 and on server B, the current identifier is 4. After the maximum synchronization identifier is set on server A, now it will be 4. I want the new identifier on the server to be only 5, but on the actual in fact, he inserts 6. How can I solve this problem

+4
source share
5 answers

Great editing: (much better) Option 1:

( : @VladimirBaranov , , , SEQUENCE )

- , , , . , , TSQL, , . : . SQL Server 2012 2014 :

CREATE SEQUENCE oddNums
  START WITH 1
  INCREMENT BY 2;
GO

CREATE SEQUENCE evenNums
  START WITH 0
  INCREMENT BY 2;
GO

AUTO INCREMENT PK, DEFAULT SEQUENCE ( ):

CREATE TABLE oddMirror (
 [id] int PRIMARY KEY DEFAULT NEXT VALUE FOR oddNums, 
 [data] varchar(7)
);

CREATE TABLE evenMirror (
 [id] int PRIMARY KEY DEFAULT NEXT VALUE FOR evenNums, 
 [data] varchar(7)
);

, PK .

SQLFiddle.

, IDENTITY, (- DEFAULT), ​​ id, .

, , , SEQUENCE , ( ) , SEQUENCE.

, MSDN, SEQUENCE SQL Server.

( ) 2:

(: ). , , - , , , . - , .

, :

CREATE TABLE oddMirror
(id INT NOT NULL IDENTITY(1,2),
data NVARCHAR(10))
GO


CREATE TABLE evenMirror
(id INT NOT NULL IDENTITY(2,2),
data NVARCHAR(10)
GO

, , reset "" . , oddMirror:

DECLARE @maxId INT
DECLARE @newSeed INT
SET @maxId = (SELECT MAX(id) FROM oddMirror)
SET @newSeed = (SELECT CASE WHEN @maxId % 2 = 1 THEN @maxId ELSE @maxId -1 END)

DBCC CHECKIDENT('dbo.oddMirror', RESEED, @newSeed)
GO

evenMirror:

DECLARE @maxId INT
DECLARE @newSeed INT
SET @maxId = (SELECT MAX(id) FROM evenMirror)
SET @newSeed = (SELECT CASE WHEN @maxId % 2 = 0 THEN @maxId ELSE @maxId -1 END)

DBCC CHECKIDENT('dbo.evenMirror', RESEED, @newSeed)
GO

, oddMirror : " id, , , , ".

"evenMirror", , max id .

:

oddMirror
1,"one"
3,"three"
5,"five"


evenMirror
2,"two"
4,"four"
6,"six"
8,"eight"

(, evenMirror )

:

oddMirror
1,"one"
2,"two"
3,"three"
4,"four"
5,"five"
6,"six"
8,"eight"
--evenMirror looks the same as this now

:

MAX(id) on oddMirror - 8. 8 % 2 = 0, @newSeed = 8 - 1 = 7, , oddMirror id = 9.

MAX(id) on evenMirror 8, . 8 % x = 0, @newSeed = 8, 'evenMirror will get id = 10`

id = 7 , , .

:

INSERT INTO oddMirror (data) VALUE ("data")
GO

INSERT INTO evenMirror (data) VALUE ("otherData")
GO

:

oddMirror
1,"one"
2,"two"
3,"three"
4,"four"
5,"five"
6,"six"
8,"eight"
9,"data"


evenMirror
1,"one"
2,"two"
3,"three"
4,"four"
5,"five"
6,"six"
8,"eight"
10,"otherData"

, , WHEN CASE , , , . , (GUID) , , (SEQUENCE s) , , , .

, . , , . , "" (, 3-4 - ), , , , , .

, , db, , , , , id db .

+3

, . .

, CHECKIDENT , , MAX ID CHECKIDENT.

, MSDN , :

- SQL Server , . . , . NOCACHE , .

, CHECKIDENT, , .

, CHECKIDENT :

Caller sysadmin , db_owner db_ddladmin .

, IDENTITY(1,1), IDENTITY(-1,-1). , IDs , .

script, , - .

-- Sample data
CREATE TABLE #T1 (ID bigint IDENTITY(1,1), V1 int);
CREATE TABLE #T2 (ID bigint IDENTITY(-1,-1), V2 int);

INSERT INTO #T1 VALUES (11);
INSERT INTO #T1 VALUES (12);
INSERT INTO #T1 VALUES (13);
INSERT INTO #T1 VALUES (14);

INSERT INTO #T2 VALUES (21);
INSERT INTO #T2 VALUES (22);
INSERT INTO #T2 VALUES (23);

SELECT * FROM #T1;
SELECT * FROM #T2;

:

#T1
ID  V1
1   11
2   12
3   13
4   14

#T2 
ID  V2
-1  21
-2  22
-3  23


-- Insert into T1 new values from T2
SET IDENTITY_INSERT #T1 ON;

MERGE INTO #T1 AS Dst
USING
(
    SELECT ID, V2
    FROM #T2
) AS Src
ON Dst.ID = Src.ID
WHEN NOT MATCHED BY TARGET
THEN INSERT (ID, V1)
VALUES (Src.ID, Src.V2);

SET IDENTITY_INSERT #T1 OFF;

-- Insert into T2 new values from T1
SET IDENTITY_INSERT #T2 ON;

MERGE INTO #T2 AS Dst
USING
(
    SELECT ID, V1
    FROM #T1
) AS Src
ON Dst.ID = Src.ID
WHEN NOT MATCHED BY TARGET
THEN INSERT (ID, V2)
VALUES (Src.ID, Src.V1);

SET IDENTITY_INSERT #T2 OFF;

SELECT * FROM #T1;
SELECT * FROM #T2;

-

#T1
ID  V1
1   11
2   12
3   13
4   14
-1  21
-2  22
-3  23


#T2
ID  V2
-1  21
-2  22
-3  23
1   11
2   12
3   13
4   14

, ,

-- Insert more data into T1 and T2
INSERT INTO #T1 VALUES (15);
INSERT INTO #T1 VALUES (16);

INSERT INTO #T2 VALUES (24);
INSERT INTO #T2 VALUES (25);
INSERT INTO #T2 VALUES (26);

SELECT * FROM #T1;
SELECT * FROM #T2;

-- Clean up
DROP TABLE #T1;
DROP TABLE #T2;


#T1
ID  V1
1   11
2   12
3   13
4   14
-1  21
-2  22
-3  23
5   15
6   16

#T2
ID  V2
-1  21
-2  22
-3  23
1   11
2   12
3   13
4   14
-4  24
-5  25
-6  26

, T1 , T2 .

+3

:
:

  • [pkId]: PK.
  • [src]: integer -or any other as you want- field and update it from 1st. source data by 1 and for the 2nd one by 2`.
  • [Id]: , .
  • [nId]: bigint, null.

, nId:

Update <table> 
Set nId = isnull((select count(ti.*) from <table> as ti where ti.pkId < <table>.pkId), 0) + 1

.

, .

Edit :

pkId | src | Id | nId before query | nId after query
-----+-----+----+------------------+--------------------
 1   | 1   | 1  | null             | 1
 2   | 2   | 1  | null             | 2
 3   | 1   | 2  | null             | 3
 5   | 2   | 2  | null             | 4
 6   | 2   | 3  | null             | 5
 8   | 1   | 3  | null             | 6
+1

, (1,1), (-1, -1). , "" .

, .

+1

, / . , node , .

? , .

node? 1-X node 1 X + 1-Y Node # 2? , .

BIGINT:

Node 1:1-200000000000 (200 ) Node 2: 200000000001-600000000000 (400 )

600000000001 . . , .

, DBCC CHECKIDENT RESEED. , / .

, , , node .

0
source

More articles:


All Articles