Définir et modifier un filtre de colonne

S’applique à : SQL Server (toutes les versions prises en charge) Azure SQL Managed Instance

Cette rubrique explique comment définir et modifier un filtre de colonne dans SQL Server en utilisant SQL Server Management Studio ou Transact-SQL.

Dans cette rubrique

Avant de commencer

Limitations et restrictions

  • Certaines colonnes ne peuvent pas être filtrées. Pour plus d’informations, consultez Filtrer des données publiées. Si vous modifiez un filtre de colonne après que des abonnements ont été initialisés, vous devez générer un nouvel instantané et réinitialiser tous les abonnements après avoir effectué la modification. Pour plus d’informations sur les exigences relatives aux changements de propriétés, consultez Changer les propriétés des publications et des articles.

Utilisation de SQL Server Management Studio

Définissez des filtres de colonnes sur la page Articles de l'Assistant Nouvelle Publication. Pour plus d’informations sur l’utilisation de l’Assistant Nouvelle publication, consultez Créer une publication.

Définissez et modifiez des filtres de colonne dans la page Articles de la boîte de dialogue Propriétés de la publication - <Publication> . Pour plus d’informations sur les propriétés des publications et des articles, consultez Afficher et modifier les propriétés d’une publication.

Pour définir un filtre de colonne

  1. Sur la page Articles de l'Assistant Nouvelle publication, développez la table à filtrer dans le volet Objets à publier .

  2. Activez la case à cocher en regard des colonnes que vous voulez filtrer.

Pour modifier le filtrage des colonnes

  1. Dans la page Articles de la boîte de dialogue Propriétés de la publication - <Publication> , développez la table à filtrer dans le volet Objets à publier .

  2. Désactivez la case à cocher en regard de chaque colonne que vous voulez filtrer, et vérifiez que la case à cocher est activée pour chaque colonne qui doit être incluse dans l'article.

  3. Sélectionnez OK.

Utilisation de Transact-SQL

Lors de la création d'articles de table, vous pouvez définir les colonnes à inclure dans l'article et modifier ces colonnes une fois l'article défini. Vous pouvez créer et modifier par programme des colonnes filtrées en utilisant des procédures stockées de réplication.

Notes

Les procédures suivantes supposent que la table sous-jacente n'a pas été modifiée. Pour plus d’informations sur la réplication des modifications du langage de définition de données (DDL) sur les tables publiées, consultez Modifier le schéma dans les bases de données de publication.

Pour définir un filtre de colonne pour un article publié dans une publication transactionnelle ou d'instantané

  1. Définissez l'article à filtrer. Pour plus d’informations, consultez définir un Article.

  2. Dans la base de données de publication sur le serveur de publication, exécutez sp_articlecolumn. Les colonnes à inclure ou à supprimer de l'article sont alors définies.

    • En cas de publication de quelques colonnes d'une table en contenant de nombreuses, exécutez sp_articlecolumn une fois pour chaque colonne ajoutée. Spécifiez le nom de colonne pour @column et une valeur d’ajout pour @operation.

    • Si vous publiez la plupart des colonnes d’une table avec de nombreuses colonnes, exécutez sp_articlecolumn, en spécifiant une valeur Null pour @column et une valeur d’ajout pour @operation pour ajouter toutes les colonnes. Exécutez ensuite sp_articlecolumn, une fois pour chaque colonne exclue, en spécifiant une valeur de suppression pour @operation et le nom de colonne exclu pour @column.

  3. Dans la base de données de publication sur le serveur de publication, exécutez sp_articleview. Spécifiez le nom de la publication pour @publication et le nom de l’article filtré pour @article. Les objets de synchronisation pour l'article filtré sont alors créés.

Pour modifier un filtre de colonne de manière à inclure des colonnes supplémentaires pour un article publié dans une publication transactionnelle ou d'instantané

  1. Dans la base de données de publication sur le serveur de publication, exécutez sp_articlecolumn une fois pour chaque colonne ajoutée. Spécifiez le nom de colonne pour @column et une valeur d’ajout pour @operation.

  2. Dans la base de données de publication sur le serveur de publication, exécutez sp_articleview. Spécifiez le nom de la publication pour @publication et le nom de l’article filtré pour @article. Si la publication a des abonnements existants, spécifiez la valeur 1 pour @change_active. Les objets de synchronisation pour l'article filtré sont alors recréés.

  3. Exécutez de nouveau le travail de l'Agent d'instantané pour la publication afin de générer un instantané mis à jour.

  4. Réinitialiser les abonnements. Pour plus d’informations, consultez Réinitialiser des abonnements.

Pour modifier un filtre de colonne de manière à supprimer des colonnes pour un article publié dans une publication transactionnelle ou d'instantané

  1. Dans la base de données de publication sur le serveur de publication, exécutez sp_articlecolumn une fois pour chaque colonne supprimée. Spécifiez le nom de la colonne pour @column et une valeur de suppression pour @operation.

  2. Dans la base de données de publication sur le serveur de publication, exécutez sp_articleview. Spécifiez le nom de la publication pour @publication et le nom de l’article filtré pour @article. Si la publication a des abonnements existants, spécifiez la valeur 1 pour @change_active. Les objets de synchronisation pour l'article filtré sont alors recréés.

  3. Exécutez de nouveau le travail de l'Agent d'instantané pour la publication afin de générer un instantané mis à jour.

  4. Réinitialiser les abonnements. Pour plus d’informations, consultez Réinitialiser des abonnements.

Pour définir un filtre de colonne pour un article publié dans une publication de fusion

  1. Définissez l'article à filtrer. Pour plus d’informations, consultez définir un Article.

  2. Dans la base de données de publication sur le serveur de publication, exécutez sp_mergearticlecolumn. Les colonnes à inclure ou à supprimer de l'article sont alors définies.

    • En cas de publication de quelques colonnes d'une table en contenant de nombreuses, exécutez sp_mergearticlecolumn une fois pour chaque colonne ajoutée. Spécifiez le nom de colonne pour @column et une valeur d’ajout pour @operation.

    • Si vous publiez la plupart des colonnes d’une table avec de nombreuses colonnes, exécutez sp_mergearticlecolumn, en spécifiant une valeur Null pour @column et une valeur d’ajout pour @operation pour ajouter toutes les colonnes. Exécutez ensuite sp_mergearticlecolumn, une fois pour chaque colonne exclue, en spécifiant une valeur de suppression pour @operation et le nom de colonne exclu pour @column.

Pour modifier un filtre de colonne de manière à inclure des colonnes supplémentaires pour un article publié dans une publication de fusion

  1. Dans la base de données de publication sur le serveur de publication, exécutez sp_mergearticlecolumn une fois pour chaque colonne ajoutée. Spécifiez le nom de colonne pour @column, une valeur d’ajout pour @operation et une valeur de 1 pour @force_invalidate_snapshot et @force_reinit_subscription.

  2. Exécutez de nouveau le travail de l'Agent d'instantané pour la publication afin de générer un instantané mis à jour.

  3. Réinitialiser les abonnements. Pour plus d’informations, consultez Réinitialiser des abonnements.

Pour modifier un filtre de colonne de manière à supprimer des colonnes pour un article publié dans une publication de fusion

  1. Dans la base de données de publication sur le serveur de publication, exécutez sp_mergearticlecolumn une fois pour chaque colonne supprimée. Spécifiez le nom de colonne pour @column, une valeur de suppression pour @operation et une valeur de 1 pour @force_invalidate_snapshot et @force_reinit_subscription.

  2. Exécutez de nouveau le travail de l'Agent d'instantané pour la publication afin de générer un instantané mis à jour.

  3. Réinitialiser les abonnements. Pour plus d’informations, consultez Réinitialiser des abonnements.

Exemple (Transact-SQL)

Dans cet exemple de réplication transactionnelle, la colonne DaysToManufacture est supprimée d'un article reposant sur la table Product .

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

Dans cet exemple de réplication de fusion, la colonne CreditCardApprovalCode est supprimée d'un article reposant sur la table SalesOrderHeader .

DECLARE @publication AS sysname;
DECLARE @table1 AS sysname;
DECLARE @table2 AS sysname;
DECLARE @table3 AS sysname;
DECLARE @salesschema AS sysname;
DECLARE @hrschema AS sysname;
DECLARE @filterclause AS nvarchar(1000);
SET @publication = N'AdvWorksSalesOrdersMerge'; 
SET @table1 = N'Employee'; 
SET @table2 = N'SalesOrderHeader'; 
SET @table3 = N'SalesOrderDetail'; 
SET @salesschema = N'Sales';
SET @hrschema = N'HumanResources';
SET @filterclause = N'Employee.LoginID = HOST_NAME()';

-- Add a filtered article for the Employee table.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table1, 
  @source_object = @table1, 
  @type = N'table', 
  @source_owner = @hrschema,
  @schema_option = 0x0004CF1,
  @description = N'article for the Employee table',
  @subset_filterclause = @filterclause;

-- Add an article for the SalesOrderHeader table that is filtered
-- based on Employee and horizontally filtered.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table2, 
  @source_object = @table2, 
  @type = N'table', 
  @source_owner = @salesschema, 
  @vertical_partition = N'true',
  @schema_option = 0x0034EF1,
  @description = N'article for the SalesOrderDetail table';

-- Add an article for the SalesOrderDetail table that is filtered
-- based on SaledOrderHeader.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table3, 
  @source_object = @table3, 
  @source_owner = @salesschema,
  @description = 'article for the SalesOrderHeader table', 
  @identityrangemanagementoption = N'auto', 
  @pub_identity_range = 100000, 
  @identity_range = 100, 
  @threshold = 80,
  @schema_option = 0x0004EF1;

-- Add all columns to the SalesOrderHeader article.
EXEC sp_mergearticlecolumn 
  @publication = @publication, 
  @article = @table2, 
  @force_invalidate_snapshot = 1, 
  @force_reinit_subscription = 1;

-- Remove the credit card Approval Code column.
EXEC sp_mergearticlecolumn 
  @publication = @publication, 
  @article = @table2, 
  @column = N'CreditCardApprovalCode', 
  @operation = N'drop', 
  @force_invalidate_snapshot = 1, 
  @force_reinit_subscription = 1;

-- Add a merge join filter between Employee and SalesOrderHeader.
EXEC sp_addmergefilter 
  @publication = @publication, 
  @article = @table2, 
  @filtername = N'SalesOrderHeader_Employee', 
  @join_articlename = @table1, 
  @join_filterclause = N'Employee.BusinessEntityID = SalesOrderHeader.SalesPersonID', 
  @join_unique_key = 1, 
  @filter_type = 1, 
  @force_invalidate_snapshot = 1, 
  @force_reinit_subscription = 1;

-- Add a merge join filter between SalesOrderHeader and SalesOrderDetail.
EXEC sp_addmergefilter 
  @publication = @publication, 
  @article = @table3, 
  @filtername = N'SalesOrderDetail_SalesOrderHeader', 
  @join_articlename = @table2, 
  @join_filterclause = N'SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID', 
  @join_unique_key = 1, 
  @filter_type = 1, 
  @force_invalidate_snapshot = 1, 
  @force_reinit_subscription = 1;
GO

Voir aussi

Modifier les propriétés des publications et des articles
Filtrer des données publiées
Filtrer des données publiées en vue de la réplication de fusion