rs_truncate

Truncates a table or a table partition in a replicate database.

Examples

Example 1

Replaces the existing rs_truncate function string for the <authors> table with one that executes a Transact-SQL delete command, which logs all deletions, instead of the truncate table command, which does not log deletions.

alter function string authors.rs_truncate
 for sqlserver_derived_class
 output language
 'delete authors'

You would want to customize the rs_truncate function string for the <authors> table, if:

The replicate database does not support the Transact-SQL truncate table command, or
You want to have deletions logged at the replicate database.

Example 2

Replaces the existing rs_truncate function string for the <publisher >table to replicate truncate table partition as a delete command:

alter function string publisher.rs_truncate
  for rs_sqlserver_function_class
  output language
  'begin transaction
    if (?1!param? = '''') /* No parameter */
      delete publisher
    if (?1!param? = ''A'')
      delete publisher where c1 < 1000
    if (?1!param? = ''B'')
      delete publisher where c1 >= 1000
commit transaction'

Example 3

Alters the function string to do nothing if there is a parameter so that table partitions are not truncated at replicate:

alter function string publisher.rs_truncate
  for rs_sqlserver_function_class
  output language
  'if(?1!param? = '''') delete publisher'

Usage

rs_truncate has a replication definition scope. Replication Server executes it to truncate a table or one or more table partitions.
Replication Server generates an rs_truncate function string for the system-provided function-string classes when you create the replication definition.
If you use a user-created base function-string class, create an rs_truncate function string for each replication definition.
Create or customize an rs_truncate function string at the Replication Server where you created the replication definition.
The default-generated function string for rs_truncate, for the <rs_sqlserver_function_class> and <rs_default_function_class> classes for each replication definition, uses the Transact-SQL truncate table command syntax. It deletes all rows in a table without logging the deletion of each individual row.
Replication Server will reconstruct the same command executed at the primary site. This command requires that the replicate site to have the same partition names. If not, DSI will shut down.
The partition names are passed as parameters to the rs_truncate function. rs_truncate function string accepts position-based function-string parameters. The following is a position-based variable:
```
?n!param?
```
The function-string variable ?1!param? corresponds to the first parameter in the rs_truncate function.
A function string has a minimum version of 1500 if it contains position-based function-string variables. A replication definition has a minimum version of at least 1500 if it contains a 1500 function string.

Table 1: Function String Variable Modifiers
Modifier	Description
`<new>`, `<new_raw>`	A reference to the new value of a column in a row you are inserting or updating
`<old>`, `<old_raw>`	A reference to the existing value of a column in a row you are updating or deleting
`<user>`, `<user_raw>`	A reference to a variable that is defined in the input template of an rs_select or rs_select_with_lock function string
`<sys>`, `<sys_raw>`	A reference to a system-defined variable
`<param>`, `<param_raw>`	A reference to a function parameter
`<text_status>`	A reference to or a function parameter. If the parameter is not defined through function replication definition or user defined function (create function), there must be a number between 1 and 99 (with no leading 0) in place of parameter name which states the parameter position in the function in the LTL command.