History table physical implementation

You saw in one of the previous sections that you can create your own history table and associate it with a current table or you can let SQL Server do it for you. When SQL Server creates a history table, by default it creates a row stored clustered table with a clustered index on period columns. If the current table does not contain data types that prevents the usage of data compression, the table is created with PAGE compression. Is this OK? Well, it depends on the use case. This approach is good, if dominant temporal queries are based on date range that is, return a snapshot for all rows that were valid at the given point in time. However, if your temporal queries usually look for historical records for individual items, a clustered index on primary key columns followed by period columns would be a better solution.

Finally, if you plan to process a lot of data in temporal queries or to aggregate them, the best approach is to create your own history table...