Data change recording
SQL Server 2019 (15.x) provides two features that track changes to data in a database: change data capture and change tracking.
These features enable applications to determine the DML changes (insert, update, and delete operations) that were made to user tables in a database. Change data capture and change tracking can be enabled on the same database; no special considerations are required.
Change tracking is supported by SQL Database. Change data capture is only supported in SQL Server and Azure SQL Database Managed Instance.
Check the following link to make sure your edition's capturing and tracking is supported https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016?view=sql-server-ver15
So what is tracking and what is capturing?
The tracking mechanism in change data capture involves an asynchronous capture of changes from the transaction log so that changes are available after the DML operation.
In change tracking, the tracking mechanism involves synchronous tracking of changes in line with DML operations so that change information is available immediately.
In other words, capturing historical information and tracking current information (https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/track-data-changes-sql-server?view=sql-server-ver15).
Change tracking records insert, update, and delete activity applied to tracked tables, supplying the details of the changes in an easily consumed relational format. For functions return information about the changes, see https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/change-tracking-functions-transact-sql?view=sql-server-ver15
Change data capture records insert, update, and delete activity applied to SQL Server tables, supplying the details of the changes in an easily consumed relational format. Column information that mirrors the column structure of a tracked source table is captured for the modified rows, along with the metadata needed to apply the changes to a target environment. For functions are used to return information about the changes, see https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/change-data-capture-functions-transact-sql?view=sql-server-ver15
This means that change data is made available to change data capture consumers through table-valued functions (TVFs). All queries of these functions require two parameters to define the range of Log Sequence Numbers (LSNs) that are eligible for consideration when developing the returned result set. Both the upper and lower LSN values that bound the interval are considered to be included within the interval (https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/work-with-change-data-sql-server?view=sql-server-ver15).
It is worth noting, these functions are system functions rather than user defined functions, which are compact pieces of Transact SQL code, which can accept parameters, and return either a value, or a table. Data transformation and reference value retrieval are common uses for functions. User Defined Functions enable the developer or DBA to create functions of their own, and save them inside SQL Server (http://venkateswarlu.net/SQLServe/User_Defined_Functions_in_sql_server.aspx).
One of the advantages of User Defined Functions over Stored Procedures, is the fact that a UDF can be used in a Select, Where, or Case statement. They also can be used to create joins. In addition, User Defined Functions are simpler to invoke than Stored Procedures from inside another SQL statement. However, User Defined Functions cannot be used to modify base table information. The DML statements INSERT, UPDATE, and DELETE cannot be used on base tables. Another disadvantage is that SQL functions that return non-deterministic values are not allowed to be called from inside User Defined Functions. GETDATE is an example of a non-deterministic function. Every time the function is called, a different value is returned. Therefore, GETDATE cannot be called from inside a UDF you create.
One method I've seen quite often is to have audit tables. Then you can show just what's changed, what's changed and what it changed from, or whatever you heart desires :) Then you could write up a trigger to do the actual logging. Not too painful if done properly...https://stackoverflow.com/questions/38437/how-to-track-data-changes-in-a-database-table
Example:
You need to implement the following auditing rules for the Employees table:
- Record any changes that are made to the data in the Employees table.
- Customize the data that is recorded by the audit operations.
Solution 1: You implement a check constraint on the Employees table.
Solution 2: You implement a stored procedure on the Employees table.
Solution 3: You implement a user-defined function on the Employees table.
Which solution meet the goal?
Answer: We should use table-valued functions, not procedures, to customize the recorded change data.