The difference between a datetime vs a datetime2 for this seems not very relevant for just capturing entry date the precision difference between the two is within the margin...

Thanks Jeff this looks fantastic! I truly appreciate the time you took to put this together.

I updated the CTEs to the best of my understanding but I can not...

I have no clue about why that would be.  It was an observation especially but not limited to what should have been VARCHAR data...

There have been a lot of ideas...

First, thanks Jeffrey and Steve for the feedback.

Also, I'll state that I never make final performance decisions based on what's in an execution plan and so I'm going to have...

p.s.  Since you've not been around since the 90's, you might want to have a look at the introduction to a technique that is extremely useful for a whole lot...

Thank you Jeff.

It was just an example code from a book I'm reading intended to exemplify the use of WHILE in TSQL. Maybe it's not the best example, but...

The reason for the Derived Table "X" is so that they can use a WHERE clause to control how may rows are returned much like would be done using a...

Ed, what's your take on using Column Store for column-oriented (they record the entity and the column that changed as well as the "OldValue" and the "NewValue {big mistake on...

Sometimes, that's difficult because a lot of technical manuals aren't...

Now, I don't want to hijack this thread into EF tuning... but there are code smells in LINQ just like there are in T-SQL. My pet peeve is join...

So.. just to be sure from your example data...

101 has never been changed.

102 was replaced by 103 which was replaced by 104.

Is that correct?

If so, a hierarchical recursive CTE (rCTE)...

For me and just IMHO...

If it's a brand new database, I like to deploy using a restore.

If it's updates to a previously deployed database, then I like scripts because you...

The best way is to not read the same area of the table that he's writing to.  It's also important that his "loading" should be fast.  Some will suggest the...

p.s.  If the table isn't too wide, consider the use of a "System Versioned Temporal Table" which is all of that done auto-magically for you.

https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables

https://docs.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-table