Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Filters data that are published based on a table article. This stored procedure is executed at the Publisher on the publication database.
sp_articlefilter [ @publication = ] 'publication' , [ @article = ] 'article' [ , [ @filter_name = ] 'filter_name' ] [ , [ @filter_clause = ] 'filter_clause' ] [ , [ @force_invalidate_snapshot = ] force_invalidate_snapshot ] [ , [ @force_reinit_subscription = ] force_reinit_subscription ] [ , [ @publisher = ] 'publisher' ]
sp_articlefilter is used in snapshot replication and transactional replication.
Executing sp_articlefilter for an article with existing subscriptions requires that those subscriptions to be reinitialized.
sp_articlefilter creates the filter, inserts the ID of the filter stored procedure in the filter column of the sysarticles (Transact-SQL) table, and then inserts the text of the restriction clause in the filter_clause column.
To create an article with a horizontal filter, execute sp_addarticle (Transact-SQL) with no filter parameter. Execute sp_articlefilter, providing all parameters including filter_clause, and then execute sp_articleview (Transact-SQL), providing all parameters including the identical filter_clause. If the filter already exists and if the type in sysarticles is 1 (log-based article), the previous filter is deleted and a new filter is created.
If filter_name and filter_clause are not provided, the previous filter is deleted and the filter ID is set to 0.
DECLARE @publication AS sysname; DECLARE @table AS sysname; DECLARE @filterclause AS nvarchar(500); DECLARE @filtername AS nvarchar(386); DECLARE @schemaowner AS sysname; SET @publication = N'AdvWorksProductTran'; SET @table = N'Product'; SET @filterclause = N'[DiscontinuedDate] IS NULL'; SET @filtername = N'filter_out_discontinued'; SET @schemaowner = N'Production'; -- Add a horizontally and vertically filtered article for the Product table. -- Manually set @schema_option to ensure that the Production schema -- is generated at the Subscriber (0x8000000). EXEC sp_addarticle @publication = @publication, @article = @table, @source_object = @table, @source_owner = @schemaowner, @schema_option = 0x80030F3, @vertical_partition = N'true', @type = N'logbased', @filter_clause = @filterclause; -- (Optional) Manually call the stored procedure to create the -- horizontal filtering stored procedure. Since the type is -- 'logbased', this stored procedures is executed automatically. EXEC sp_articlefilter @publication = @publication, @article = @table, @filter_clause = @filterclause, @filter_name = @filtername; -- Add all columns to the article. EXEC sp_articlecolumn @publication = @publication, @article = @table; -- Remove the DaysToManufacture column from the article EXEC sp_articlecolumn @publication = @publication, @article = @table, @column = N'DaysToManufacture', @operation = N'drop'; -- (Optional) Manually call the stored procedure to create the -- vertical filtering view. Since the type is 'logbased', -- this stored procedures is executed automatically. EXEC sp_articleview @publication = @publication, @article = @table, @filter_clause = @filterclause; GO