Best practices for history/temporal tables?

Suppose I have an object, with certain fields which I want to track history, and certain fields which I do not want to track history. From a normalization perspective, is the following schema alright:

CREATE TABLE MyObject AS (
    MyObjectId INT IDENTITY NOT NULL PRIMARY KEY,
    MyObjectField1 VARCHAR(100) NOT NULL,
    MyObjectField2 VARCHAR(100) NOT NULL,
    MyObjectField3 VARCHAR(100) NOT NULL,
    MyObjectTrackedField1 VARCHAR(100) NOT NULL,
    MyObjectTrackedField2 VARCHAR(100) NOT NULL,
    MyObjectTrackedField3 VARCHAR(100) NOT NULL,
)
CREATE TABLE MyObjectHistory AS (
    MyObjectHistoryId INT IDENTITY NOT NULL PRIMARY KEY,
    MyObjectId INT NOT NULL FOREIGN KEY REFERENCES MyObject(MyObjectId),
    MyObjectTrackedField1 VARCHAR(100) NOT NULL,
    MyObjectTrackedField2 VARCHAR(100) NOT NULL,
    MyObjectTrackedField3 VARCHAR(100) NOT NULL,
)

where MyObjectHistory contains the tracked fields for all except for the latest revision. Or, should all tracked fields be in one table, and all revisions including the latest be in that table, as in:

CREATE TABLE MyObject AS (
    MyObjectId INT IDENTITY NOT NULL PRIMARY KEY,
    MyObjectField1 VARCHAR(100) NOT NULL,
    MyObjectField2 VARCHAR(100) NOT NULL,
    MyObjectField3 VARCHAR(100) NOT NULL,
)
CREATE TABLE MyObjectHistory AS (
    MyObjectHistoryId INT IDENTITY NOT NULL PRIMARY KEY,
    MyObjectId INT NOT NULL FOREIGN KEY REFERENCES MyObject(MyObjectId),
    MyObjectTrackedField1 VARCHAR(100) NOT NULL,
    MyObjectTrackedField2 VARCHAR(100) NOT NULL,
    MyObjectTrackedField3 VARCHAR(100) NOT NULL,
)

For practical data access reasons, you should use the structure from your first option, but instead keep all versions of your tracked column values including the current version in your history table.

The reason for this is that in general, when you want to look at history, you want to include the present and all past versions. When you don't want to look at history, you want it out of the way. In many cases this means going so far as to segregate the history into a separate schema or database altogether. Even if you keep your history in the same schema as your current data, any queries that look at historical data (including the current values) will be much more complex since they have to essentially union two sources.

StackExchange Database Administrators Q#35627, answer score: 7