Defining Articles

A published table is called an article. You have a number of options for controlling the way a table is published. An article can be the entire table or a subset of columns and/or rows from the table.

For each article, you can set the article name, article description, and destination table name; exclude rows and columns from those published (horizontal and vertical partitioning); customize the synchronization schema script; and customize the data replication mechanism. You accomplish these tasks by using the Manage Article dialog box.

To define an article

While creating a publication, from the Edit Publications dialog box, select the Article tab; from the Articles in Publication list, select an article; and then choose the Edit button.
For information about how to access the Edit Publications dialog box, see Creating Publications, earlier in this chapter.
The Manage Article dialog box appears.
To change the default name supplied for the article, type a new name in the Name box.
To provide a description of this article, type it in the Description box.
A description can be up to 255 characters.
To change the default name supplied for the destination table, type a table name in the Destination Table box.
To partition the article vertically or horizontally, select the Filters tab.
To partition the article vertically¾so that only selected columns are replicated¾select the Replicate option box for each column to be replicated. Clear the Replicate option box for each column that will not be replicated.
If you do not modify the default settings, all columns are replicated. You must replicate the primary key column.
To partition the article horizontally¾so that only selected rows are replicated¾type a WHERE clause in the Restriction Clause box.
For example, if you have published the authors table from the pubs database and are editing the authors article, you could replicate only those rows containing authors from California by typing:
```
state = 'CA'
```
To customize the data replication mechanism or the synchronization schema script, select the Scripts tab.
Define the data replication mechanism.
Usually, you will not change this from the default. The data replication mechanism determines the action SQL Server takes when the log reader process encounters transactions (INSERT, UPDATE, or DELETE) that are marked for replication in the transaction log of the publication database.
- To use the default, do not change the selected options for the INSERT Command, UPDATE Command, and DELETE Command.
  When the log reader process encounters a transaction of one of these types (INSERT, UPDATE, or DELETE) that is marked for replication in the transaction log of the publication database, the SQL statement will be reconstructed and applied to the destination database. This is the default data replication mechanism used by SQL Server replication.
- To specify custom processing for one or more of the command types (INSERT, UPDATE, or DELETE), choose the Custom option, and then enter a custom command in the Custom box. There are two possible custom commands.
  If you enter NONE, transactions of that type will not be replicated. For example, if for DELETE you choose the Custom option, and type NONE, then DELETE commands will not be replicated.
  If you enter CALL stored_procedure_name (for example, if you type CALL MYPROC), when the log reader encounters a command of that type (INSERT, DELETE, or UPDATE) in a transaction marked for replication, it will pass column parameters from that transaction to the referenced stored procedure. The referenced stored procedure must exist in the destination database of each subscriber.
  For information about creating your own stored procedures to be called when INSERT, DELETE, or UPDATE statements are encountered by the log reader, see Chapter 15, Advanced Replication.
To allow SQL Server to automatically create the initial table synchronization schema script for you, leave the Creation Script box blank.
When you edit a new article, this box is empty. When you edit an existing article, this box contains the full path to the schema script in the distribution working directory. For example:
```
\\TERRIER\C$\SQL60\REPLDATA\terrier_sales_receipts_charges.sch
```
Schema scripts are automatically generated when a publication is created or modified (by choosing the Add or Modify button from the Edit Publications dialog box).
To edit an existing schema creation script, choose Edit, edit the creation script in the Creation Script dialog box that appears, and then choose OK.

The Manage Article dialog box returns.
To generate a schema script now, choose the Generate button and complete the Auto-Generate Sync Scripts dialog box that appears.
- Select Do Nothing when you do not want to drop, truncate, or delete rows from the destination table before synchronization is applied.
- Select DROP Table First to drop the existing table before synchronization is applied.
- Select Truncate Table Only to remove all rows but leave the table structure in place before synchronization is applied.
- Select Delete Data using Restriction Clause to remove only the target rows (as defined by the restriction clause) before synchronization is applied.
- Select Transfer Clustered Index to create in the destination table the same clustered index that exists for the published table.
- Select Transfer non-Clustered Index to create in the destination table the same non-clustered indexes that exist for the published table.
- Select Convert UDDT to base datatype to convert user-defined datatypes from the published table to base datatypes in the destination table.
- Select Include DRI-PK to copy the declared primary key to the destination table.
When the options are set, choose Auto-Generate. The initial table synchronization schema script is created. The Manage Article dialog box returns.
To use a schema creation script that you have prepared manually, place that schema script in the distribution working directory, and then enter the full path and filename in the Creation Script box. For example:
```
\\TERRIER\C$\SQL60\REPLDATA\terrier_sales_receipts_myschema.sch
```
Note that you enter the full UNC path even if this is a combined publisher/distributor using its own local distribution working directory. Use the administrative share name (ending in "$") for the drive, rather than the drive letter.
To specify custom filtering, select the Advanced button and complete the Manage Article-Advanced dialog box that appears.
- To specify a custom filter stored procedure, select a stored procedure from the list in the Filter Stored Procedure box.
  This is a filter stored procedure that you have previously created in the publication database. This stored procedure must take the form:
  IF sql_statement RETURN 1, ELSE RETURN 0
  where sql_statement is a Transact-SQL statement that accomplishes filtering. For example:
```
IF exists (select * from authors where city = 'Oakland')
   return 1
else
   return 0
```
  Because the filter is executed against every log record for the replicated table, the filter should be as simple as possible. This will minimize overhead.
  Any reference to the table will represent only a single log record value.
- To set the synchronization object to an object other than the default, select the object from the list in the Synchronization Object box. If you are setting up custom filtering, this will be a view you have created in the publication database.
  The Synchronization Object is the database object that is used as the source of the data for the initial synchronization of the destination table. In most cases, this is handled automatically by SQL Server and you do not need to change from the default. By default, the synchronization object is the article's base table. If the article has been horizontally and/or vertically partitioned, SQL Server automatically creates a view of the partitioned table, and by default that view becomes the synchronization object.
  The Synchronization Object list includes all the tables and views in the publication database.
When the options are set, choose OK. The Manage Article dialog box returns.
When the advanced article is defined, from the Manage Article dialog box, choose OK.
The Edit Publications dialog box returns.

If you create an auto-generated script that does not support the creation of indexes, then the synchronization task will use fast bcp as the initial synchronization method if the Select Into/Bulk Copy option is set in the destination database.