Définir et modifier un filtre de jointure entre des articles de fusion

Article
05/28/2013

Cette rubrique décrit comment définir et modifier un filtre de jointure entre des articles de fusion dans SQL Server 2012 à l'aide de SQL Server Management Studio ou de Transact-SQL. La réplication de fusion prend en charge les filtres de jointure, qui sont en général utilisés conjointement aux filtres paramétrables pour étendre le partitionnement de table à d'autres articles de table connexes.

Dans cette rubrique

Avant de commencer :

Limitations et restrictions

Recommandations
Pour définir et modifier un filtre de jointure entre des articles de fusion à l'aide de :

SQL Server Management Studio

Transact-SQL

Avant de commencer

Limitations et restrictions

Pour qu'il soit possible de créer un filtre de jointure, la publication doit contenir au minimum deux tables associées. Un filtre de jointure permet d'étendre un filtre de lignes : vous devez donc définir un filtre de lignes sur une table pour pouvoir étendre le filtre à une autre table avec une jointure. Après avoir défini un filtre de jointure, vous pouvez l'étendre avec un autre filtre de jointure si la publication contient des tables associées supplémentaires.
Si vous ajoutez, modifiez ou supprimez un filtre de jointure après que les abonnements à la publication aient é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 conditions requises pour les modifications des propriétés, consultez Modifier les propriétés des publications et des articles.

Recommandations

Les filtres de jointure peuvent être créés manuellement pour un ensemble de tables, ou bien la réplication peut générer les filtres automatiquement sur la base des relations entre les clés étrangères et les clés primaires définies sur les tables. Pour plus d'informations sur la génération automatique d'un ensemble de filtres de jointure, consultez générer automatiquement un ensemble de filtres de jointure entre des articles de fusion (SQL Server Management Studio).

Icône de flèche utilisée avec le lien Retour en haut [Top]

Utilisation de SQL Server Management Studio

Définissez, modifiez et supprimez des filtres de jointure sur la page Filtrer les lignes de la table de l'Assistant Nouvelle publication ou sur la page Filtrer les lignes de la boîte de dialogue Propriétés de la publication - <publication>. Pour plus d'informations sur l'utilisation de cet Assistant et sur l'accès à cette boîte de dialogue, consultez Créer une publication et Afficher et modifier les propriétés d'une publication.

Pour définir un filtre de jointure

Sur la page Filtrer les lignes de la table de l'Assistant Nouvelle publication ou sur la page Filtrer les lignes de la boîte de dialogue Propriétés de la publication - <publication>, sélectionnez un filtre de lignes ou un filtre de jointure existant dans le volet Tables filtrées.
Cliquez sur Ajouter, puis sur Ajouter une jointure pour étendre le filtre sélectionné.
Créez l'instruction de jointure : sélectionnez Utiliser le générateur pour créer l'instruction ou Créer manuellement l'instruction de jointure.
- Si vous choisissez d'utiliser le générateur, utilisez les colonnes de la grille (Conjonction, Colonne de table filtrée, Opérateur et Colonne de table jointe) pour créer une instruction de jointure.
  
  Chaque colonne de la grille contient une zone de liste déroulante qui vous permet de sélectionner deux colonnes et un opérateur (=, <>, <=, <, >=, >, et like). Les résultats sont affichés dans la zone de texte Aperçu. Si la jointure concerne plus d'une paire de colonnes, sélectionnez une conjonction (AND ou OR) dans la colonne Conjonction, et entrez deux autres colonnes et un opérateur.
- Si vous choisissez d'écrire l'instruction manuellement, écrivez l'instruction de jointure dans la zone de texte Instruction de jointure. Utilisez la zone de liste Colonnes de table filtrée et la zone de liste Colonnes de table jointe pour faire glisser et déposer des colonnes dans la zone de texte Instruction de jointure.
- L'instruction de jointure complète est par exemple celle-ci :
```
SELECT <published_columns> FROM [Sales].[SalesOrderHeader] INNER JOIN [Sales].[SalesOrderDetail] ON [SalesOrderHeader].[SalesOrderID] = [SalesOrderDetail].[SalesOrderID]
```
  La clause JOIN doit utiliser un nommage en deux parties ; les nommages en trois et en quatre parties ne sont pas pris en charge.

Spécifiez les options de jointure :

Si la colonne sur laquelle vous effectuez la jointure dans la table filtrée (la table parente) est unique, sélectionnez Clé unique.

Attention
La sélection de cette option indique que la relation entre les tables enfants et parent dans un filtre de jointure est « un à un » ou « un à plusieurs ». Sélectionnez cette option seulement si vous avez une contrainte sur la colonne de jointure dans la table enfant qui garantit l'unicité. Si cette option est définie incorrectement, il peut se produire une non-convergence des données.

Par défaut, la réplication de fusion traite les modifications sur une base ligne par ligne lors de la synchronisation. Pour que les modifications dans les lignes de la table filtrée et de la table jointe soient traitées comme une unité, sélectionnez Enregistrement logique (Microsoft SQL Server 2005 et ses versions ultérieures seulement). Cette option est disponible seulement si les conditions requises par l'article et par la publication pour l'utilisation des enregistrements logiques sont satisfaites. Pour plus d'informations, consultez la section « Considérations sur l'utilisation des enregistrements logiques » dans la rubrique Regrouper les modifications apportées à des lignes connexes à l'aide d'enregistrements logiques.

Cliquez sur OK.
Si vous êtes dans la boîte de dialogue Propriétés de la publication - <publication>, cliquez sur OK pour enregistrer et fermer la boîte de dialogue.

Pour modifier un filtre de jointure

Sur la page Filtrer les lignes de la table de l'Assistant Nouvelle publication ou sur la page Filtrer les lignes de la boîte de dialogue Propriétés de la publication - <publication>, sélectionnez un filtre dans le volet Tables filtrées, puis cliquez sur Modifier.
Dans la boîte de dialogue Modifier une jointure, modifiez le filtre.
Cliquez sur OK.

Pour supprimer un filtre de jointure

Sur la page Filtrer les lignes de la table de l'Assistant Nouvelle publication ou sur la page Filtrer les lignes de la boîte de dialogue Propriétés de la publication - <publication>, sélectionnez un filtre dans le volet Tables filtrées, puis cliquez sur Supprimer. Si le filtre de jointure que vous supprimez est lui-même étendu par d'autres jointures, ces jointures seront aussi supprimées.

Icône de flèche utilisée avec le lien Retour en haut [Top]

Utilisation de Transact-SQL

Ces procédures montrent un filtre paramétrable sur un article parent avec des filtres de jointure entre cet article et des articles enfants connexes. Les filtres de jointure peuvent être définis et modifiés par programme à l'aide des procédures stockées de réplication.

Pour définir un filtre de jointure pour étendre un filtre d'article aux articles connexes dans une publication de fusion

Définissez le filtrage pour l'article auquel s'effectue la jointure, qui est également connu comme l'article parent.
- Pour un article filtré à l'aide d'un filtre de lignes paramétrable, consultez Définir et modifier un filtre de lignes paramétrable pour un article de fusion.
- Pour un article filtré à l'aide d'un filtre de lignes statique, consultez Définir et modifier un filtre de lignes statiques.
Exécutez sp_addmergearticle (Transact-SQL) sur la base de données de publication du serveur de publication pour définir un ou plusieurs articles connexes, qui sont également connus comme les articles enfants, pour la publication. Pour plus d'informations, consultez Définir un article.

Exécutez sp_addmergefilter (Transact-SQL) sur la base de données de publication du serveur de publication. Spécifiez @publication, le nom unique de ce filtre pour @filtername, le nom de l'article enfant créé à l'étape 2 pour @article, le nom de l'article parent auquel s'effectue la jointure pour @join_articlename et l'une des valeurs suivantes pour @join_unique_key :

0 – indique une jointure plusieurs-à-un ou plusieurs-à-plusieurs entre les articles parents et enfants.
1 – indique une jointure un-à-un ou un-à-plusieurs entre les articles parents et enfants.

Cela définit un filtre de jointure entre les deux articles.

Attention
Affectez uniquement à @join_unique_key la valeur 1 s'il existe une contrainte sur la colonne de jointure dans la table sous-jacente pour l'article parent qui garantit l'unicité. Si @join_unique_key se voit affecter la valeur 1 de manière inappropriée, une non-convergence des données peut se produire.

Exemples (Transact-SQL)

Cet exemple définit un article pour une publication de fusion, où l'article de la table SalesOrderDetail est filtré par rapport à la table SalesOrderHeader qui est elle-même filtrée à l'aide d'un filtre de ligne statique. Pour plus d'informations, consultez Définir et modifier un filtre de lignes statiques.

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

Cet exemple définit un groupe d'articles dans une publication de fusion où les articles sont filtrés à l'aide d'une série de filtres de jointure par rapport à la table Employee, qui est elle-même filtrée à l'aide d'un filtre de lignes paramétrable sur la valeur de HOST_NAME dans la colonne LoginID. Pour plus d'informations, consultez Définir et modifier un filtre de lignes paramétrable pour un article de fusion.

-- To avoid storing the login and password in the script file, the value 
-- is passed into SQLCMD as a scripting variable. For information about 
-- how to use scripting variables on the command line and in SQL Server
-- Management Studio, see the "Executing Replication Scripts" section in
-- the topic "Programming Replication Using System Stored Procedures".

--Add a new merge publication.
DECLARE @publicationdb AS sysname;
DECLARE @publication AS sysname;
DECLARE @table1 AS sysname;
DECLARE @table2 AS sysname;
DECLARE @filter AS sysname;
DECLARE @schema_hr AS sysname;
DECLARE @schema_sales AS sysname;

SET @publicationdb = N'AdventureWorks2012';
SET @publication = N'AdvWorksSalesPersonMerge';
SET @table1 = N'Employee';
SET @table2 = N'SalesPerson';
SET @filter = N'SalesPerson_Employee';
SET @schema_hr = N'HumanResources';
SET @schema_sales = N'Sales';

USE [AdventureWorks2012];

-- Enable AdventureWorks2012 for merge replication.
EXEC sp_replicationdboption
  @dbname = @publicationdb,
  @optname = N'merge publish',
  @value = N'true';  

-- Create new merge publication with Subscriber requested snapshot
-- and using the default agent schedule. 
EXEC sp_addmergepublication 
  @publication = @publication, 
  @description = N'Merge publication of AdventureWorks2012.', 
  @allow_subscriber_initiated_snapshot = N'true',
  @publication_compatibility_level = N'90RTM';

-- Create a new snapshot job for the publication, using the default schedule.
-- Pass credentials at runtime using sqlcmd scripting variables.
EXEC sp_addpublication_snapshot 
  @publication = @publication, 
  @job_login = $(login), 
  @job_password = $(password);

-- Add an article for the Employee table, 
-- which is horizontally partitioned using 
-- a parameterized row filter.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table1, 
  @source_owner = @schema_hr, 
  @source_object = @table1, 
  @type = N'table', 
  @description = 'contains employee information', 
  @subset_filterclause = N'[LoginID] = HOST_NAME()';

-- Add an article for the SalesPerson table, 
-- which is partitioned based on a join filter.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table2, 
  @source_owner = @schema_sales, 
  @source_object = @table2, 
  @type = N'table', 
  @description = 'contains salesperson information';

-- Add a join filter between the two articles.
EXEC sp_addmergefilter 
  @publication = @publication, 
  @article = @table1, 
  @filtername = @filter, 
  @join_articlename = @table2, 
  @join_filterclause = N'[Employee].[BusinessEntityID] = [SalesPerson].[SalesPersonID]', 
  @join_unique_key = 1, 
  @filter_type = 1;
GO

-- Start the agent job to generate the full snapshot for the publication.
-- The filtered data snapshot is generated automatically the first time 
-- the subscription is synchronized. 
DECLARE @publication AS sysname;
SET @publication = N'AdvWorksSalesPersonMerge';

EXEC sp_startpublication_snapshot 
  @publication = @publication;
GO

Icône de flèche utilisée avec le lien Retour en haut [Top]