I want to create a table with the "current" values. Each time any characteristic / column in that table changes I want to add a new row.
I was planning to have an [EndDate] column which would start with '1999-12-31 00:00:00' and when the row changed I would update the original row, change the EndDate to "Now", and create a new row with the current values.
I also have a [CreateDate] column (which would be created with the exact same value as was set on the old row in [EndDate]
My plan was to join associated tables to this table ON the MAX([EndDate]) > [Table2CreateDate]
But perhaps there is a better way?
This isn't actually for a Prices table, but I think that would be similar? I want to store [sys.indexes] whenever it changes so I can link a historical log of the Index DMVs to whatever was the then-current values in the [sys.indexes] table.
One problem I have is that there is no persistent PKey on [sys.indexes]. If I drop/recreate the table, and then recreate the indexes in a different order, the Key fields in [sys.indexes] will be different, so I don't see there being any point Updating my [sys.indexes] copy (e.g. having a TRIGGER store the previous values in an AUDIT table], but rather I will create a new row whenever anything changes, and mark the old entry as "expired on"
But maybe this is not the best way to do it?