Keep count of records of one table in another-CodePudding

I have a table that has records in it, customer purchase info (table a). I want to make a reference table (table b) that keep tracks of how many purchases each person has made by running a count against table a. So say Customer 1 has made 15 purchases that are stored in Table A, Table B would have the customers name and then showing a count of their 15 purchases. Was wondering the best way to go about this. Thanks!

CodePudding user response：

Denormalizing this information into a separate table is a bad idea. You risk update anomalies, and need to write complex and inefficient trigger code to keep it up to date.

You could create a view for this. Your best bet here is an Indexed View, which the server will maintain for you, and allow efficient querying.

The main restrictions on an indexed view (as opposed to a normal view) are:

Only inner joins are allowed, no applys or subqueries or derived tables or CTEs.
Grouping is allowed, but COUNT_BIG(*) must be included, and the only other aggregation allowed is SUM
Must be schema-bound (all two-part tables references, no changes to underlying columns)

CREATE OR ALTER VIEW dbo.TotalPurchase
WITH SCHEMABINDING
AS

SELECT
  p.CustomerId,
  NumPurchases = COUNT_BIG(*),
  PurchaseAmount = SUM(p.Amount)
FROM dbo.Purchase p
GROUP BY
  p.CustomerId;

go

CREATE UNIQUE CLUSTERED INDEX IX_TotalPurchase
  ON TotalPurchase (CustomerId)
  WITH (DROP_EXISTING = ON);

You can now query this view as if it was a normal table, and the server will ensure it is kept up to date in real-time.

I do recommend using the NOEXPAND hint when querying it, for various reasons.

SELECT *
FROM TotalPurchase tp WITH (NOEXPAND);