As Jeff Moden as pointed out, for performance reasons, it's likely to best to create a physical tally table (if you don't already have one) to use for this query:

I would think this would be the most straightforward way, although it may not be the most efficient way.  But it might work just fine for you if...

Actually the use of "parent" and "child" predates databases, for example with directories.  Parent directory and child directory were very commonly used terms.

As to tables, not every referenced table is...

I would think this would be the most straightforward way, although it may not be the most efficient way.  But it might work just fine for you if your data...

My personal opinion is that this has all the ear-markings of a classic invoice/invoice detail or work-order/work-order detail problem and should be handled as two individual tables rather than...

Or you could do this 😉

SELECT IDENTITY(int, 1, 1) AS id, A.*,B.* 
INTO ##C 
FROM A 
JOIN B ON  X = Y

No, there's no direct way to do that.

However, you could use a standard identity column for the second part of the key, and use ROW_NUMBER() to get a sequential number...

Since you always want to list all recording artists, I'd start at that table.  I would think it's possible that info on an album by a new artist might not...

A common approach to fix that issue in general is to isolate the SUM() in a separate query, so that the later join doesn't increase the total.  For example, something...

Be sure to cluster the table on ( MyDate, $IDENTITY ).  The $IDENTITY just insures that the key is unique.  That will make the deletes very efficient.

Yes, it is very important here, to ensure that the nonclus index key is unique.

And, besides, all clustered index key column(s) will always be added to every nonclus index whether...

I changed to using descriptive variable names.  And I added some debugging displays to help you work through any issues.  The sample number you provided is failing the test, but...

Make the nonclus index UNIQUE yourself, don't force SQL to do it for you.  This is important for best performance.

And, yes, you should include the DateDeleted in the index.  I...

I don't think the current method is a bad approach.

You can use a filtered index to identify the rows needing recalculated.

IF the calcs were easy, I guess you could put...

Are you genuinely on SQL 2016 or higher?

If so, I think CHOOSE should save you a lot of coding here.