{"id":1933,"date":"2023-05-01T05:00:38","date_gmt":"2023-05-01T12:00:38","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=1933"},"modified":"2023-04-28T14:54:33","modified_gmt":"2023-04-28T21:54:33","slug":"change-tracking-in-azure-sql-database","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/change-tracking-in-azure-sql-database\/","title":{"rendered":"Change Tracking in Azure SQL Database"},"content":{"rendered":"

The Azure SQL Database has two main ways to track changes<\/a> with data (rows\/DML) as well as table changes. One of those methods is Change Tracking<\/a> with the other being Change Data Capture<\/a>. Today\u2019s post will be going into depth on Change Tracking.<\/p>\n

Change Tracking and Change Data Capture<\/h2>\n

So, what\u2019s the difference between the two? Change Data Capture<\/strong> provides historical change information while Change Tracking<\/strong> captures the fact that rows in a table were changed but doesn\u2019t capture the data that was changed. Here is a quick look up table from the documentation:<\/p>\n

\n\n\n\n\n\n\n\n\n\n\n
Feature<\/th>\nChange data capture<\/th>\nChange tracking<\/th>\n<\/tr>\n<\/thead>\n
Tracked changes<\/strong><\/td>\n<\/td>\n<\/td>\n<\/tr>\n
DML changes<\/td>\nYes<\/td>\nYes<\/td>\n<\/tr>\n
Tracked information<\/strong><\/td>\n<\/td>\n<\/td>\n<\/tr>\n
Historical data<\/td>\nYes<\/td>\nNo<\/td>\n<\/tr>\n
Whether column was changed<\/td>\nYes<\/td>\nYes<\/td>\n<\/tr>\n
DML type<\/td>\nYes<\/td>\nYes<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n

So why would I use Change Tracking over Change Data Capture? To start, if you don\u2019t need to know about historical data and just want what has changed over a set time period, Change Tracking is for you. Seeing it tells you what has changed and not the actual data that has changed, storage and processing overhead is going to be much less. As for use cases, say you have a table that you have an ETL pipeline run every night to fill a data warehouse\/lake house for analytics. Using Change Tracking, you can get the final version of all the rows that have changed in the past 24 hours and not pull the entire table over for a truncate\/replace operation. This significantly reduces the amount of data moved as well as processing time.<\/p>\n

Sample Data<\/h2>\n

Let\u2019s take this ETL use case and see this Change Tracking in action. To start, create a new Azure SQL Database called CTDemo<\/strong> and use the AdventureWorksLT<\/strong> sample data. You can add this sample data in the Azure Portal when creating a new Azure SQL Database. It\u2019s under the Additional Settings<\/strong> tab.<\/p>\n

\"Image<\/a><\/p>\n

And once the database is up and running, I can use Azure Data Studio to quickly connect to it. Here I called my database CTDemo.<\/p>\n

\"Image<\/a><\/figure>\n

Now that we have connected to the database, we can take a quick look at the sample tables and the data in them.<\/p>\n

\"Image<\/a><\/figure>\n

Enable Change Tracking<\/h2>\n

The table we are interested in is the SalesLT.SalesOrderDetail table. Here we can turn on change tracking and create this sample ETL extract but only include the rows that have been modified (insert\/update\/delete).<\/p>\n

Open a new query window and run the following SQL as the sqladmin user to enable Change Tracking:<\/p>\n

ALTER DATABASE CTDemo  \r\nSET CHANGE_TRACKING = ON  \r\n(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);\r\nGO<\/code><\/pre>\n
\"Image<\/a><\/figure>\n
Two things to note here. First, the CHANGE_RETENTION<\/strong> parameter. This is used to tell Change Tracking how long to keep the changes around in the tracking tables. The AUTO_CLEANUP<\/strong> parameter is used to enable or disable the cleanup task that removes old change tracking information that is older than the CHANGE_RETENTION period.<\/p>\n
Next, enable the table to be tracked with Change Tracking:<\/p>\n
ALTER TABLE [SalesLT].[SalesOrderDetail] \r\nENABLE CHANGE_TRACKING  \r\nWITH (TRACK_COLUMNS_UPDATED = ON)\r\nGO<\/code><\/pre>\n
The parameter TRACK_COLUMNS_UPDATED is used to store extra information about which columns were updated to the internal change tracking table. This can be used later to help with ETL and query jobs to find the exact column with the changes but will add some storage overhead seeing we are keeping more information in the tracking tables.<\/p>\n
When you enable Change Tracking on a table, it automatically creates a side table to store the changes with the naming format sys.change_tracking_tableID, with tableID being the ID of the table.<\/p>\n
\n
\n
Change Tracking Version Numbers<\/p>\n<\/div>\n
\n
To understand what change tracking is doing, we need to understand the version numbers it has and how it uses them as well as the side tables it creates.<\/p>\n
As you just read, when you enable change tracking on a table, it creates a side table which contains the information on which rows have changed. There is also a system table called syscommittab that is created when DB is created regardless of if change tracking is on or not that is used. These are the tables that are used in conjunction with the version numbers to know what changes to keep and which changes to remove due to the retention period we set when enabling Change Tracking.<\/p>\n
Along with these tables are the version numbers. To start, we have the current version number and the function to get this number, CHANGE_TRACKING_CURRENT_VERSION(). This number is associated with the last committed transaction on the table that is being tracked. You alter the data in the table via an insert, update, or delete, this number will increase by one. Next version number is the minimum valid version with the corresponding function CHANGE_TRACKING_MIN_VALID_VERSION(). This version number is the minimum number that we can use to get change tracking information from our tracked tables. Data in the tracking tables that have a number lower than the minimum valid version will have been removed by the cleanup jobs again based on your retention period.<\/p>\n
As we use Change Tracking in practice in this post, these version numbers and how to use them will become clearer.<\/p>\n<\/div>\n<\/div>\n
We can use the following SQL to query the Change Tracking DMV that contains information about which tables are enabled. This uses the object_name(object_id) function to take in the table ID that\u2019s in the sys.change_tracking_tables and returns the table name to provide more information than just a number for the table.<\/p>\n
select object_name(object_id) [table],* from sys.change_tracking_tables;\r\nGO<\/code><\/pre>\n
\"Image<\/a><\/figure>\n
Before we go any further, let\u2019s explain what these columns are:<\/p>\n
sys.change_tracking_tables <\/strong>Columns<\/p>\n\n\n\n\n\n\n\n\n\n
Column name<\/th>\nDescription<\/th>\n<\/tr>\n<\/thead>\n
object_id<\/td>\n\u00a0the ID of the table<\/td>\n<\/tr>\n
is_track_columns_updated_on<\/td>\n\u00a0the current state of change tracking on a table with 1 being on and 0 being off<\/td>\n<\/tr>\n
begin_version<\/td>\n\u00a0Version of the database when change tracking started for the table. begin_version starts at 0, when a transaction is committed, it gets incremented by one \u2013 new tables get the current version number<\/td>\n<\/tr>\n
cleanup_version<\/td>\n\u00a0Version up to which cleanup might have removed change tracking information.<\/td>\n<\/tr>\n
min_valid_version<\/td>\n\u00a0Minimum valid version of change tracking information that is available for the table.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 <\/p>\n
Working with Change Tracking<\/h2>\n
Now that we have the background, time to use this in practice. Start with the following query to pull all the line items of order number 71784 from the SalesLT.SalesOrderDetail table:<\/p>\n
select * from [SalesLT].[SalesOrderDetail]\r\nwhere SalesOrderID = 71784 \r\norder by SalesOrderDetailID\r\nGO<\/code><\/pre>\n
This oder has 43 lines to it. We can issue a update to this order by giving all the lines with a quantity greater than 8 a 10% discount with the following SQL:<\/p>\n
update SalesLT.SalesOrderDetail\r\nset UnitPriceDiscount = '0.10'\r\nwhere SalesOrderID = 71784  \r\nand OrderQty > 8;\r\nGO<\/code><\/pre>\n
Ten rows updated, now let\u2019s look at the change tracking information for this table. Now with what we know about version numbers, we can look at the current version and expect to see 1. Why a 1? Because one transaction took place, the update we just performed:<\/p>\n
select CHANGE_TRACKING_CURRENT_VERSION() 'Current Version';\r\nGO<\/code><\/pre>\n
\"Image<\/a><\/figure>\n
To look at the tracking information, we need to use the CHANGETABLE function. The function takes the following:\nCHANGES\u00a0table_name<\/em>\u00a0,\u00a0last_sync_version<\/em>\n<\/strong>Which returns tracking information for all changes to the table you passed in that have occurred since the version that is specified by\u00a0last_sync_version<\/em>. If you pass in null, it will bring back all changes in the table from the retention period forward. If you pass in a later version number, you will get only a subset of changes.<\/p>\n
\n
\n
This last_sync_version<\/em> will be critical if you have an ETL process that runs every 24 hours, but the retention period is 48 hours. The ETL process should be asking for the CHANGE_TRACKING_CURRENT_VERSION and storing that value for the next run so that all changes from that point on are seen. In our example, we are just starting out so we would pass in null to get the initial set of changes but store the value of CHANGE_TRACKING_CURRENT_VERSION for the next job. Say 4 transactions happen and we start our query again. Passing in 1 would get the changes from 1 and up with the CHANGE_TRACKING_CURRENT_VERSION now being 5. We store the value 5 and use it with the next query.<\/p>\n<\/div>\n<\/div>\n
SELECT\r\n *\r\nFROM\r\n CHANGETABLE(CHANGES SalesLT.SalesOrderDetail, null) as CT;\r\nGO<\/code><\/pre>\n
\"Image<\/a><\/figure>\n
And the columns being the following:<\/p>\n
\n\n\n\n\n\n\n\n\n\n\n
Column name<\/th>\nDescription<\/th>\n<\/tr>\n<\/thead>\n
SYS_CHANGE_VERSION<\/td>\nVersion value that is associated with the last change to the row<\/td>\n<\/tr>\n
SYS_CHANGE_CREATION_VERSION<\/td>\nVersion values that are associated with the last insert operation.<\/td>\n<\/tr>\n
SYS_CHANGE_OPERATION<\/td>\nSpecifies the type of change:\nU<\/strong>\u00a0= Update\nI<\/strong>\u00a0= Insert\nD<\/strong>\u00a0= Delete<\/td>\n<\/tr>\n
SYS_CHANGE_COLUMNS<\/td>\nLists the columns that have changed since the last_sync_version (the baseline). The column names are masked so use CHANGE_TRACKING_IS_COLUMN_IN_MASK<\/a> to interpret it.<\/td>\n<\/tr>\n
SYS_CHANGE_CONTEXT<\/td>\nChange context information that you can optionally specify by using the\u00a0WITH<\/a>\u00a0clause as part of an INSERT, UPDATE, or DELETE statement.<\/td>\n<\/tr>\n
<primary key column value><\/td>\nThe primary key values for the tracked table. These values uniquely identify each row in the user table.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n
Now that we have our changes, we can create an ETL query that we might use to pull the changed rows and apply them into another database for reporting and analytics:<\/p>\n
SELECT D.*\r\nFROM SalesLT.SalesOrderDetail AS D\r\nINNER JOIN CHANGETABLE(CHANGES SalesLT.SalesOrderDetail, 0) AS CT\r\nON D.SalesOrderID = CT.SalesOrderID \r\nAND D.SalesOrderDetailID = CT.SalesOrderDetailID;\r\nGO<\/code><\/pre>\n
\"Image<\/a><\/figure>\n
Playing with Numbers<\/h2>\n
Now that we have an understanding on how change tracking initially works, let\u2019s play with some version numbers. We can take a line of this order and give it a \u201cextra special discount for good customers\u201d of 100%. Now Line total is a computed column so when it updates with this discount or the previous update we did, the column will not trigger a change tracking event. Run the following SQL for our special discount. We are going to use the SYS_CHANGE_CONTEXT function to add some flavor:<\/p>\n
declare @ct_context varbinary(128) = convert(varbinary(128), N'extra special discount for good customers');\r\n\r\nwith CHANGE_TRACKING_CONTEXT (@ct_context)\r\nupdate SalesLT.SalesOrderDetail\r\nset UnitPriceDiscount = '1.00'\r\nwhere SalesOrderDetailID = 110762;\r\nGO<\/code><\/pre>\n
Look at the change tracking table again. First, let\u2019s pass in null:<\/p>\n
SELECT *\r\nFROM CHANGETABLE(CHANGES SalesLT.SalesOrderDetail, null) as CT;\r\nGO<\/code><\/pre>\n
\"Image<\/a><\/figure>\n
We see 11 rows: the 10 rows from our first update and the one from our last update. You can see the new row has a SYS_CHANGE_VERSION of 2.<\/p>\n
Limit the results<\/h2>\n
Back to that ETL process use case. If the process was run with 0 or null, it would again get the initial 10 updates and the new update but that\u2019s not what we want, we want only the new changes. If the process took and stored the CHANGE_TRACKING_CURRENT_VERSION which was 1, it would now pass it into the query, and we would see the following:<\/p>\n
\"Image<\/a><\/figure>\n
That\u2019s a little better. You could imagine a table with thousands of changes a day and getting just the changes from the last ETL process again will help with ETL size and processing time. If you looked at the SYS_CHANGE_CONTEXT column, you do see it needs to be converted into a varchar. Use the following SQL for that:<\/p>\n
SELECT convert(varchar(128),sys_change_context) sys_change_context_text, *\r\nFROM CHANGETABLE(CHANGES SalesLT.SalesOrderDetail, 1) as CT;\r\nGO<\/code><\/pre>\n
\"Image<\/a><\/figure>\n
And our ETL pipe query would then use the CHANGE_TRACKING_CURRENT_VERSION and run the following query:<\/p>\n
SELECT D.*\r\nFROM SalesLT.SalesOrderDetail AS D\r\nINNER JOIN CHANGETABLE(CHANGES SalesLT.SalesOrderDetail, 1) AS CT\r\nON D.SalesOrderID = CT.SalesOrderID \r\nAND D.SalesOrderDetailID = CT.SalesOrderDetailID;\r\nGO<\/code><\/pre>\n
\"Image<\/a><\/figure>\n
And as expected, we only see the details for that single row update. How about inserts and deletes? We can run some of those now and see what we get. Starting with an insert, we can insert a line into our SalesLT.SalesOrderDetail table:<\/p>\n
insert into SalesLT.SalesOrderDetail (SalesOrderID, OrderQty, ProductID, UnitPrice, UnitPriceDiscount)\r\nvalues (71784, 100, 999, '343.6496', '0.50');\r\nGO<\/code><\/pre>\n
And view the order with the following SQL:<\/p>\n
select * from [SalesLT].[SalesOrderDetail]\r\nwhere SalesOrderID = 71784\r\nand ProductID = 999;\r\nGO<\/code><\/pre>\n
\"Image<\/a><\/figure>\n
The CHANGE_TRACKING_CURRENT_VERSION was 2 before this transaction happened so we use that in our query of the change tracking table. Remember the flow would be:<\/p>\n
    \n
  1. use stored value<\/li>\n
  2. get changes<\/li>\n
  3. get current value<\/li>\n
  4. and store it for the next query<\/li>\n<\/ol>\n
    SELECT *\r\nFROM CHANGETABLE(CHANGES SalesLT.SalesOrderDetail, 2) as CT;\r\nGO<\/code><\/pre>\n
    \"Image<\/a><\/figure>\n
    And our ETL query would show this inserted row as well:<\/p>\n
    SELECT D.*\r\nFROM SalesLT.SalesOrderDetail AS D\r\nINNER JOIN CHANGETABLE(CHANGES SalesLT.SalesOrderDetail, 2) AS CT\r\nON D.SalesOrderID = CT.SalesOrderID \r\nAND D.SalesOrderDetailID = CT.SalesOrderDetailID;\r\nGO<\/code><\/pre>\n
    \"Image<\/a><\/figure>\n
    What if I were to delete this row I just inserted? And what if my current version was still at 1? What happens? Seeing it\u2019s another transaction, does passing in 2 show the insert and passing in 3 show the delete? Let\u2019s take a look.<\/p>\n
    Delete the row using the SalesOrderDetailID. For my query, its 113407:<\/p>\n
    delete from SalesLT.SalesOrderDetail\r\nwhere SalesOrderDetailID = 113407;<\/code><\/pre>\n
    So what happens when I pass in the previous query using the 2 as the last_sync_value?<\/p>\n
    \"Image<\/a><\/figure>\n
    It shows the row as deleted as it should. Even though this row was created in a previous transaction, it no longer exists so for data consistency, it is marked as deleted no matter how many updates it might of had or when it was inserted. And passing in 3 gives the same results as above with the row marked as deleted. Very nice feature indeed!<\/p>\n
    Clean Up<\/h2>\n
    Let\u2019s change the change retention period see what happens:<\/p>\n
    ALTER DATABASE CTDemo SET CHANGE_TRACKING (CHANGE_RETENTION = 1 MINUTES);\r\nGO<\/code><\/pre>\n
    The change tracking auto-cleanup job run about every 30 minutes so we can force the job after we wait one minute for the rows to expire with the following SQL:<\/p>\n
    CHECKPOINT 1\r\nGO\r\n\r\nexec sys.sp_flush_CT_internal_table_on_demand\r\nGO<\/code><\/pre>\n
    \"Image<\/a><\/figure>\n
    Passing in no parameters for sys.sp_flush_CT_internal_table_on_demand will clean up all side tables for all change tracking enabled tables, the syscommittab system table, and will also update the invalid and hardened cleanup versions. You can also pass in a table name to cleanup just that tables\u2019s side table. In the above image, we see all 13 rows have been cleaned up from our side table and if you were to issue the query to get all changes from a side table with the CHANGETABLE function, you would get 0 rows:<\/p>\n
    SELECT *\r\nFROM CHANGETABLE(CHANGES SalesLT.SalesOrderDetail, 0) as CT<\/code><\/pre>\n
    Summary<\/h2>\n
    Change Tracking is a very powerful feature of the Azure SQL Database. We can use it for many use cases to extract just the rows that have changed over a set amount of time with very little overhead on the database. You can read more about Change Tracking in the documentation here. There are a few key concepts to remember; one is the version numbers are incremented by transactions. Two, remember to get and store the current version number if you are pulling for say an ETL process. That is the number you will need for the next job so that you get just the changes from that version number forward. And three, the retention period will flush the changes from all the change tracked tables so be sure to set an appropriate time that works with your process that is pulling the changes from the database.<\/p>\n
    Resources<\/h3>\n