Design Note

If you have triggers on your tables and you want them to be replicated along with your table, you might want to revisit them and add a line of code reading NOT FOR REPLICATION so that the trigger code isn't executed redundantly on the subscriber side! So, for a trigger (insert, update, or delete trigger) on the subscriber, you would use the NOT FOR REPLICATION statement for the whole trigger (placed before the AS statement of the trigger). If you want to be selective on a part of the trigger code (FOR INSERT, FOR UPDATE, FOR DELETE) you will put the NOT FOR REPLICATION immediately following the ones you don't want to execute and put nothing on the ones you do want to execute.

If you are using IDENTITY columns to automatically generate column values or to help partition your data you should use the NOT FOR REPLICATION option to keep these values intact during replication. SQL Server will replicate these identity values as they were created on the publisher if you have defined the column on the subscriber to use the NOT FOR REPLICATION statement. If new rows are inserted to subscriber tables with IDENTITY columns, SQL Server increments the identity value in the normal way (because the insert to the subscriber isn't via replication agent, it is via a normal user connection).

You can also use the NOT FOR REPLICATION option if you are using IDENTITY columns in tables and you are implementing ranges of identity values in a partitioned environment (one range of values on one publisher, another range of values on another publisher—and both subscribe and publish to each other). The following create table statements establish the identity value seed and increment for each publisher that owns a set of customerID values (like West Coast publisher and East Coast publisher):

-- Publisher Westcoast (range of custid between 1 and 1,000,000)
CREATE TABLE Customers ( CustID INT IDENTITY (1, 1)
NOT FOR REPLICATION PRIMARY KEY,
....)

-- Publisher Eastcoast (range of custid between 1,000,001 and above)
CREATE TABLE Customers ( CustID INT IDENTITY (1000001, 1)
NOT FOR REPLICATION PRIMARY KEY,
....)

It is best to use the NOT FOR REPLICATION option along with the CHECK constraint to ensure that the identity values being assigned are within the allowed range. For example

CREATE TABLE Customers
(CustID INT IDENTITY(1000001, 1)
    NOT FOR REPLICATION
    CHECK NOT FOR REPLICATION (CustID <= 2000000),
     ...,
    CONSTRAINT valid_range_pk PRIMARY KEY (CustID)
 )

One caveat is the case when you are using transactional replication with immediate-updating subscribers and only one publisher. You should not use the IDENTITY and NOT FOR REPLICATION approach described here. Instead, use the IDENTITY column at the publisher only, and have the Subscriber use INT(data type) for that column. In this way the next identity value is always generated at the publisher and only the publisher.