Vous pouvez créer APRÈS INSÉRER, METTRE À JOUR, SUPPRIMER trigger sur votre table example et une nouvelle table pour matérialiser les résultats.

Dans le déclencheur, vous êtes autorisé à utiliser n'importe quelle instruction. Vous pouvez le faire de deux manières, en fonction de la rapidité de votre requête initiale.

Par exemple, vous pouvez tronquer la table à chaque INSERT / UPDATE / DELETE puis calculer le nombre et l'insérer à nouveau (si la requête est rapide).

Ou vous pouvez vous fier aux tables insérées et supprimées qui sont des tables spéciales visibles dans le contexte du déclencheur et montrant comment les valeurs des lignes ont changé.

Par exemple, si un enregistrement existe dans la table insérée et non dans le supprimé - il s'agit d'une ou plusieurs nouvelle (s) ligne (s). Vous ne pouvez calculer le COUNT que pour eux.

Si un enregistrement n'existe que dans la table supprimé - il s'agit d'une suppression (nous devons supprimer la ligne de notre table précalculée).

Et une ligne existe dans les deux tables - il s'agit d'une mise à jour - nous devons effectuer un nouveau décompte pour l'enregistrement.

Une chose est très importante ici - ne manipulez pas les lignes une par une. Travaillez toujours par lots de lignes pour les trois cas ci-dessus ou vous vous retrouverez avec un déclencheur peu performant qui retardera les opérations CRUD avec la table d'origine.

Je ne pense pas que vous puissiez y parvenir en utilisant un CTE ni un LEFT JOIN car il y en a beaucoup restriction utilisant les vues indexées .

Solution de contournement

Je suggère de diviser la requête en deux parties:

1. Créer une vue indexée au lieu d'une expression de table commune (CTE)
2. Créer une vue non indexée qui effectue la jointure à gauche

À côté de cela, créez un index non clusterisé sur la colonne Entity dans le tableau Exemple .

Ensuite, lorsque vous interrogez le -indexée, elle utilisera des index

SELECT * FROM View WHERE Entity = 'AA'

Donc, lorsque vous exécutez la requête suivante:

--The other approach (cartesian join)
CREATE TABLE TwoRows (
    N INT primary key
)

INSERT INTO TwoRows
VALUES (1),(2)

CREATE VIEW dbo.indexedView  WITH SCHEMABINDING AS
    SELECT 
        IIF(T.N = 2, Entity, Parent) as Entity
        , COUNT_BIG(*) as CountPlusOne
        , COUNT_BIG(ALL IIF(T.N = 2, NULL, 1)) as Count
    FROM dbo.Example E1
    INNER JOIN dbo.TwoRows T
        ON 1=1
    WHERE IIF(T.N = 2, Entity, Parent) IS NOT NULL
    GROUP BY IIF(T.N = 2, Entity, Parent)
GO

CREATE UNIQUE CLUSTERED INDEX testIndex ON indexedView(Entity)

Vous pouvez voir que la vue Clustered index et Table Non-Clustered index sont utilisées dans le plan d'exécution:

Informations supplémentaires

Je n'ai pas trouvé de solutions de contournement supplémentaires pour remplacer l'utilisation de LEFT JOIN ou UNION ou CTE dans les vues indexées, vous pouvez en vérifier plusieurs questions Stackoverflow similaires:

• Indexation des vues avec un CTE
• Que remplacer la jointure gauche dans une vue pour pouvoir avoir une vue indexée?
• Créer un index sur la vue SQL avec les opérateurs UNION? Améliorera-t-il vraiment les performances?

Mise à jour 1 - Vue fractionnée vs jointure cartésienne

Pour identifier la meilleure approche, j'ai essayé de comparer les deux approches suggérées.

SELECT * FROM dbo.ExampleView_2 WHERE Entity = 'A'

J'ai pu accomplir ce que je cherchais en faisant une jointure cartésienne sur les lignes qui serait 0 count (N = 2).

Créer une table appelée deux lignes qui dupliquera les petits-enfants

DROP VIEW IF EXISTS dbo.indexedView 
CREATE VIEW dbo.indexedView  WITH SCHEMABINDING AS
    SELECT 
        IIF(T.N = 2, Entity, Parent) as Entity
        , COUNT_BIG(*) as CountPlusOne
        , COUNT_BIG(ALL IIF(T.N = 2, NULL, 1)) as Count
    FROM dbo.Example E1
    INNER JOIN dbo.TwoRows T
        ON 1=1
    WHERE IIF(T.N = 2, Entity, Parent) IS NOT NULL
    GROUP BY IIF(T.N = 2, Entity, Parent)
GO

CREATE UNIQUE CLUSTERED INDEX testIndex ON indexedView(Entity)

SELECT *
FROM indexedView

Récupère la table d'origine

DROP TABLE IF EXISTS Example
CREATE TABLE Example (
    Id INT primary key,
    Entity varchar(50),
    Parent varchar(50)
)


INSERT INTO Example
VALUES 
     (1, 'A', NULL)
    ,(2, 'AA',  'A')
    ,(3, 'AB','A')
    ,(4, 'ABA', 'AB')
    ,(5, 'ABB', 'AB')
    ,(6, 'AAA', 'AA')
    ,(7, 'AAB', 'AA')
    ,(8, 'AAC', 'AA')

Créer l'index view

DROP TABLE IF EXISTS TwoRows
CREATE TABLE TwoRows (
    N INT primary key
)

INSERT INTO TwoRows
VALUES (1),(2)

Je n'ai pas pu éviter d'utiliser COUNT_BIG(*)