There are several different methods available in SQL Server for tracking data changes.
The do it yourself method is to use triggers to write data to an archive table. Triggers on the source table can track any inserts, updates or deletes. You would have the option of simply storing the operation type, the date/time of the change, the user that made the change, or any combination of those.
For updates, you could create a record for each attribute changed, along with the new and old value. Another option is to have the archive table mimic the structure of the parent table, and store the before version of the table in that row. However, that would require changes to your trigger and archive table each time a change was made to the parent table.
The Change Tracking feature (added in SQL Server 2008) will let you know which rows have been changed. This can be useful in a scenario where updated records need to be pushed to another data store, like a copy of the parent data store, any situation where we only care that the row has changed and we just need the current version of the row.
Change Tracking is off by default, so it needs to be enabled at the database level, and then enabled for each table to be tracked. Under Properties for the database is a Change Tracking page. Here you can also specify how long to retain the change information. On the Table properties, you can enable change tracking, as well as set the Track Columns Updated option, which will track which columns have been updated. But this option won’t track the actual data that was changed.
Change Data Capture:
Change Data Capture was also added in SQL Server 2008, originally as an Enterprise edition only feature, but is now available in Standard. CDC reads from the transaction log to store data in a change table. The change table will store the operation type of the change, the data that was changed, as well as some additional metadata to relate that change to a log transaction, as well as the order of the operation within a transaction. So this would be a good solution for a true data auditing need.
CDC is also enabled at the database level (sys.sp_cdc_enable_db), and then enabled for the table to be tracked (sys.sp_cdc_enable_table).
Microsoft – Change Data Capture
This subsequent post goes more in depth with CDC
Temporal tables were added in SQL Server 2016. Temporal tables also track changes to a table, but it allows to query to a point in time, to see what the data looked like at that point. You could create a query to compare records between the current state and the state at a particular time to determine what had changed, but this feature isn’t meant to be a true auditing solution.