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 for a snapshot. What circumstances are appropriate for each?
A:
@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.
John Sansom
2010-07-29 14:46:09
Thanks John, what circumstance would make one option more desirable over the other? Assuming there is a practical difference, which is why I ask.
PMSawyer
2010-07-29 15:04:14
TRUNCATE for example is considered to be a superior performing operation than DELETE. You also cannot perform a TRUNCATE operation on a table with foreign key references. Conversely, neither DELETE nor TRUNCATE will remove objects associated with the table such as indexes, which the DROP operation will do.
John Sansom
2010-07-29 16:26:21
If I understand correctly in terms of replication if one has a table with no foreign key references use the TRUNCATE because it performs much better. Otherwise we delete data and live with the decreased performance. Thanks.
PMSawyer
2010-07-30 12:28:45
I would suggest that you go with the Replication default which is DROP, unless you have a specific need to do otherwise.
John Sansom
2010-07-30 19:00:31