Informal discussion on identity columns in SQL Server (ii)

Server
This article mainly summarizes how the identity column is handled in replication

1. Snapshot replication
In snapshot replication, you typically do not need to consider the attributes of an identity column.

2. Transactional replication
Example:
Publish database A, subscribe to database B, publication is t_test_a, subscription table is T_test_b
CREATE TABLE t_test_a
(ID int IDENTITY (1,1),
Name varchar (50)
)
CREATE TABLE T_test_b
(ID int IDENTITY (1,1),
Name varchar (50)
)

In this case, the replication agent will not be able to copy the new row to library B because the column ID is an identity column, the identity column cannot be displayed with a value, and replication fails.
At this point, you need to set the not for REPLICATION option for the identity column. Thus, when the replication agent connects to the table T_test on library B with any login, all not on that table
The For REPLICATION option will be activated to explicitly insert an ID column.

There are two kinds of situations here:
1, the T_test table of library B will not be updated by the user (or application)
In the simplest case, if the t_test of library B is not updated by the user (or the application), it is recommended that you remove the identity attribute of the ID column, using only the simple int type.

2. The T_test table of library B will be updated by other users (or applications)

In this case, the ID column of two t_test tables conflicts, for example:
Execute the following statement in library A:
INSERT t_test_a (Name) VALUES (' Tom ') (assuming ID is listed as 1)
Execute the following statement in Library B:
INSERT t_test_b (Name) VALUES (' Pip ') (assuming ID is listed as 1)
This will insert a record in the two tables of library A and library B, obviously two different records.
However, the matter is not over until the preset replication time, the replication agent attempts to insert the record "1 TOM" into the T_test table in library B, but the T_test_b table for library B already exists

A column with ID 1, the insertion will not succeed, and by duplicating the monitor we will find that the replication failed.
The solutions to these problems are:
(1) Specify a different range of values for the Publisher and Subscriber identity columns, as the previous example can be modified to:
--Ensure that the table record does not exceed 10000000
CREATE TABLE t_test_a
(ID int IDENTITY (1,1),
Name varchar (50)
)
CREATE TABLE T_test_b
(ID int IDENTITY (10000000,1),
Name varchar (50)
)
(2) The value of the identity column of the publisher and Subscriber is not duplicated, as
--Using odd values
CREATE TABLE t_test_a
(ID int IDENTITY (1,2),
Name varchar (50)
)
--Using even values
CREATE TABLE T_test_b
(ID int IDENTITY (2,2),
Name varchar (50)
)
This approach can be generalized when subscribers and publishers are around, the definition of identity column attributes are as follows
(1,4), (2,4), (3,4), (4,4)

3. Merge replication
Resolve in transactional replication, as long as the values of the publication table and subscription table identity columns are not duplicated.