Caching Aggregate Counts, Sql Server

Language: Layout:

CREATE TABLE dbo.InventoryActivity(
    InventoryActivity_uid int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Organization_uid int NOT NULL,
    MasterInventory_uid int NOT NULL,
    AdjustmentType_cd varchar(20) NULL,
    AdjustmentReason_cd varchar(20) NULL,
    Quantity int NOT NULL
)
GO

CREATE TABLE dbo.InventorySummary(
    Organization_uid int NOT NULL,
    MasterInventory_uid int NOT NULL,
    Quantity int NOT NULL,
    PRIMARY KEY (Organization_uid, MasterInventory_uid)
)
GO

CREATE TRIGGER dbo.InventoryActivity_I
ON dbo.InventoryActivity
AFTER INSERT
AS 
    
    CREATE TABLE #InsertSummaryTemp
    (
      Organization_uid int,
      MasterInventory_uid int,
      Quantity int
    )
    INSERT INTO #InsertSummaryTemp
    SELECT Organization_uid,
           MasterInventory_uid,
           SUM(Quantity) AS Quantity
    FROM INSERTED
    GROUP BY Organization_uid, MasterInventory_uid
    
    -- UPDATE EXISTING RECORDS
    UPDATE InventorySummary
    SET Quantity = s.Quantity + i.Quantity
    FROM InventorySummary s
    JOIN #InsertSummaryTemp i ON s.Organization_uid = i.Organization_uid AND 
                                 s.MasterInventory_uid = i.MasterInventory_uid
    
    -- INSERT NEW RECORDS
    INSERT INTO InventorySummary
        (Organization_uid, MasterInventory_uid, Quantity) 
    SELECT i.Organization_uid, i.MasterInventory_uid, i.Quantity
    FROM #InsertSummaryTemp i
    LEFT JOIN InventorySummary s ON i.Organization_uid = s.Organization_uid AND 
                                    i.MasterInventory_uid = s.MasterInventory_uid
    WHERE s.MasterInventory_uid IS NULL
GO

INSERT INTO InventoryActivity (Organization_uid, MasterInventory_uid, AdjustmentType_cd, AdjustmentReason_cd, Quantity) 
VALUES (1, 1, 'TRAN', 'IN',  5), 
       (1, 2, 'TRAN', 'IN',  6),
       (1, 3, 'TRAN', 'IN',  7),
       (1, 1, 'ADJ',  'IN',  8),
       (1, 2, 'ADJ',  'OUT', -3),
       (2, 1, 'ADJ',  'IN',  2),
       (1, 2, 'ADJ',  'IN',  1)
GO

INSERT INTO InventoryActivity (Organization_uid, MasterInventory_uid, AdjustmentType_cd, AdjustmentReason_cd, Quantity) 
VALUES (1, 1, 'TRAN', 'IN',  5), 
       (1, 4, 'TRAN', 'IN',  6),
       (1, 5, 'TRAN', 'IN',  7),
       (2, 1, 'ADJ',  'IN',  8),
       (2, 2, 'ADJ',  'OUT', -3),
       (2, 1, 'ADJ',  'IN',  2),
       (2, 2, 'ADJ',  'IN',  1)
GO

SELECT * FROM InventoryActivity
GO

CREATE PROCEDURE dbo.GetInventorySummary 
AS   
SELECT Organization_uid,
       MasterInventory_uid,
       SUM(Quantity) AS Quantity
FROM dbo.InventoryActivity
GROUP BY Organization_uid, MasterInventory_uid
ORDER BY Organization_uid, MasterInventory_uid
GO

CREATE VIEW dbo.InventorySummaryView
WITH SCHEMABINDING
AS
    SELECT
      Organization_uid,
      MasterInventory_uid,
      SUM(Quantity) AS Quantity,
      COUNT_BIG(*) AS Count
    FROM dbo.InventoryActivity
    GROUP BY Organization_uid, MasterInventory_uid
GO

CREATE UNIQUE CLUSTERED INDEX PK_InventorySummaryView ON dbo.InventorySummaryView
(
      Organization_uid,
      MasterInventory_uid
)
GO

SELECT '1. Stored Procedure'
GO
EXEC dbo.GetInventorySummary
GO

SELECT '2. Summary Table'
GO
SELECT * FROM dbo.InventorySummary
ORDER BY Organization_uid, MasterInventory_uid
GO

SELECT '3. Indexed View'
GO
SELECT * FROM dbo.InventorySummaryView
ORDER BY Organization_uid, MasterInventory_uid

View schema
Execution time: 0,08 sec, rows selected: 38, rows affected: 32, absolute service time: 0,22 sec