SQL Server replication drop vs truncate of target tables

The sp_addarticle stored procedure, used to create publication articles in SQL Server speak, has a parameter @pre_creation_cmd through which dropping the table or truncating data at the target server can be specified fo开发者_如何学Pythonr a snapshot. What circumstances are appropriate for each?

@pre_creation_cmd accepts one of four values:

• NONE
• DELETE
• DROP
• TRUNCATE

DELETE TABLE

Assume that your Published article data is filtered and that the corresponding table in the Subscription receives data from other sources, perhaps another Publication for example. Using the DELETE operation for this parameter would delete "only" the data the meets the criteria of the filter definition, leaving the remaining data at the Subscriber intact.

TRUNCATE TABLE

Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

DROP TABLE

Removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables.