Have you noticed the new temporal tables feature in SQL Server 2016?
Here is my take on it, for the use case of auditing.
- Must turn off the feature to add a column, then turn it back on. I consider this an upside, because the problem with using triggers is that the definition gets old and they break.
- Better than CDC (Change Data Capture), which reads the t-log , relies on Agent jobs, and is duplicating the new values and old values. Since it is LSN based, it is intended to be used to dump what you want into a different table that is easier to query.
- Better than CT (Change Tracking) does not track the actual values that changed
- The integration with the SELECT syntax is awesome.
- The system versioning must be turned off to delete records. So deletes would probably be done in a downtime, unless you want to use partitioning. I can see there being problems with runaway updates exploding the size of history tables, and then you need downtimes to clean everything up.
- Be careful with LOBs because they will be duplicated. It copies all columns. CDC might be better if you only want to track some columns.
In summary, it is best used for narrow tables with few changes, and few updates. I can picture it being perfect for an Address table, for instance, since people don’t move too often, and there is a predictable set of columns.