For the server to be able to do incremental refreshes on a table, the server must store some information about the most recent preceding refresh for that table. This refresh information is known as synchronization history data. (The synchronization history data for each table is stored in a synchronization history table. There must be one synchronization history table for each table in the publication.)
In order to make a publication incremental, you must set each table's SYNCHISTORY property, which tells the server to gather that table's synchronization history data. The command to do this is:
ALTERTABLEtable_nameSETSYNCHISTORY
You must execute this command for each table in the publication, and you must do so on both the master and replica databases.
The synchistory setting for a table is considered a "property" of that table. By default, this property is set to NOSYNCHISTORY for each table. If this property is set to SYNCHISTORY in both the master and the replica databases, then after the first refresh, subsequent refreshes to a specific publication will send the replica database only new and modified rows when the data in the table is synchronized.
It is recommendable that you set the SYNCHISTORY property for a table before it is referenced by any publication. If you want to alter the SYNCHISTORY property for a table after it has been included as part of a publication, you need to use the Sync Maintenance Mode. For details, see Upgrading the schema of a distributed system.
For example, to set the SYNCHISTORY property of the table named SYNCDEMO, use the command:
ALTER TABLE SYNCDEMO SET SYNCHISTORY; COMMIT WORK;
This statement creates a shadow table that stores history data. The shadow table tracks rows that were modified or deleted from the main table. If there are no longer any replica databases that require the data for their refreshes, the unnecessary data of the shadow table is automatically deleted.