SQL Server 2017 Developer's Guide

History table implementation

As mentioned in the Creating temporal tables section, you can create the history table in advance, or let SQL Server create it for you. In the former case, the created table is a row stored table with a clustered index on period columns. If the current table does not contain data types that prevent the usage of data compression, the table is created with PAGE compression. This physical implementation is acceptable if you query the history table by using period columns as filter criteria. However, if your temporal queries usually look for historical records for individual rows, this is not a good implementation. To check potential performance issues of an automatic created history table, use the following code ...