Specify Article Types (Replication Transact-SQL Programming)

Article
03/03/2023

Applies to: SQL Server

The default article types for replication are table articles, but you can publish other database objects as articles, including views, stored procedures, user-defined functions, and stored procedure execution. You can use replication stored procedures to specify an article type programmatically when you define an article. The procedures that you use depend on the type of replication and article type.

Note

The schema-only designation when defining table, view, and stored procedure articles indicates that only the object definition is replicated.

To publish a table article in a transactional or snapshot publication

At the Publisher on the publication database, execute sp_addarticle. Specify one of the following values for @type to define the type of article:
- logbased - a log-based table article, which is the default for transactional and snapshot replication. Replication automatically generates the stored procedure used for horizontal filtering and the view that defines a vertically filtered article.
- logbased manualfilter - a log-based, horizontally filtered article where the stored procedure used for horizontal filtering is manually created and defined by the user and specified for @filter. For more information, see Define and Modify a Static Row Filter.
- logbased manualview - a log-based, vertically filtered article where the view that defines the vertically filtered article is created and defined by the user and specified for @sync_object. For more information, see Define and Modify a Static Row Filter and Define and Modify a Column Filter.
- logbased manualboth - a log-based, horizontally and vertically filtered article where both the stored procedure used for horizontal filtering and the view that defines the vertically filtered article are created and defined by the user and specified for @filter and @sync_object, respectively. For more information, see Define and Modify a Static Row Filter and Define and Modify a Column Filter.
This defines a new article for the publication. For more information, see Define an Article.
For logbased manualboth and logbased manualfilter articles, execute sp_articlefilter to generate the filtering stored procedure for a horizontally filtered article. For more information, see Define and Modify a Static Row Filter.
For logbased manualboth, logbased manualview, and logbased manualfilter articles, execute sp_articleview to generate the view that defines the vertically filtered article. For more information, see Define and Modify a Column Filter.

To publish a view or indexed view article in a transactional or snapshot publication

At the Publisher on the publication database, execute sp_addarticle. Specify one of the following values for @type to define the type of article:
- indexed view logbased - a log-based indexed view article. Replication automatically generates the stored procedure used for horizontal filtering and the view that defines a vertically filtered article.
- view schema only - a schema-only view article. The base table must also be replicated.
- indexed view schema only - a schema-only indexed view article. The base table must also be replicated.
- indexed view logbased manualfilter - a log-based, horizontally filtered indexed view article where the stored procedure used for horizontal filtering is manually created and defined by the user and specified for @filter. For more information, see Define and Modify a Static Row Filter.
- indexed view logbased manualview - a log-based, filtered indexed view article where the view that defines a vertically filtered article is created and defined by the user and specified for @sync_object. For more information, see Define and Modify a Static Row Filter and Define and Modify a Column Filter.
- indexed view logbased manualboth - a log-based, filtered indexed view article where both the stored procedure used for horizontal filtering and the view that defines a vertically filtered article are created and defined by the user and specified for @filter and @sync_object, respectively. For more information, see Define and Modify a Static Row Filter and Define and Modify a Column Filter.
This defines a new article for the publication. For more information, see Define an Article.
For logbased manualboth and logbased manualfilter articles, execute sp_articlefilter to generate the filtering stored procedure for a horizontally filtered article. For more information, see Define and Modify a Static Row Filter.
For logbased manualboth, logbased manualview, and logbased manualfilter articles, execute sp_articleview to generate the view that defines the vertically filtered article. For more information, see Define and Modify a Column Filter.

To publish a stored procedure, stored procedure execution, or user-defined function article in a transactional or snapshot publication

At the Publisher on the publication database, execute sp_addarticle. Specify one of the following values for @typeto define the type of article:
- proc schema only - a schema-only stored procedure article.
- proc exec - replicates the execution of the stored procedure to all Subscribers of the article. For more information, see Publishing Stored Procedure Execution in Transactional Replication.
- serializable proc exec - replicates the execution of the stored procedure only if it is executed within the context of a serializable transaction. For more information, see Publishing Stored Procedure Execution in Transactional Replication.
- func schema only - a schema-only user-defined function article.
This defines a new article for the publication. For more information, see Define an Article.

To publish a table or view article in a merge publication

At the Publisher on the publication database, execute sp_addmergearticle. Specify one of the following values for @type to define the type of article:
- table - a table article.
- indexed view schema only - a schema-only indexed view article.
- view schema only - a schema-only view article.
This defines a new article for the publication. For more information, see Define an Article.

To publish a stored procedure or user-defined function article in a merge publication

At the Publisher on the publication database, execute sp_addmergearticle. Specify one of the following values for @type to define the type of article:
- func schema only - a schema-only user-defined function article.
- proc schema only - a schema-only stored procedure article.
This defines a new article for the publication. For more information, see Define an Article.