However, because maintaining column tracking information adds some extra storage overhead, this option is set to OFF by default. This can improve efficiency and performance. Column tracking can enable an application to synchronize only those columns that were updated. When the TRACK_COLUMNS_UPDATED option is set to ON, the SQL Server Database Engine stores extra information about which columns were updated to the internal change tracking table. You can also enable change tracking for a table in SQL Server Management Studio by using the Database Properties (ChangeTracking Page) dialog box. The following example shows how to enable change tracking for a table by using ALTER TABLE. When change tracking is enabled, change tracking information is maintained for all rows in the table that are affected by a DML operation. For more information, see Work with Change Tracking (SQL Server).Ĭhange tracking must be enabled for each table that you want tracked. For this reason, we strongly recommend that snapshot isolation be set to ON for the database. Using snapshot isolation is the easiest way for you to help ensure that all change tracking information is consistent. However, the CHANGETABLE function, which is used to obtain change tracking information, will return an error. If a database has a compatibility level of less than 90, you can configure change tracking. To use change tracking, the database compatibility level must be set to 90 or greater. This can be useful when there is a temporary problem that prevents applications from synchronizing and the process for removing change tracking information older than the retention period must be paused until the problem is resolved.įor any database that uses change tracking, be aware of the following: You can use the AUTO_CLEANUP option to enable or disable the cleanup task that removes old change tracking information. To avoid obtaining incorrect results, an application can use the CHANGE_TRACKING_MIN_VALID_VERSION system function to determine whether the interval between synchronizations has been too long. If an application obtains changes at longer intervals, the results that are returned might be incorrect because some of the change information has probably been removed. The specified retention period must be at least as long as the maximum time period between synchronizations. When you are setting this value, you should consider how often applications will synchronize with the tables in the database. Change tracking information that is older than this time period is removed periodically. The change retention value specifies the time period for which change tracking information is kept. You can specify the CHANGE_RETENTION and AUTO_CLEANUP options when you enable change tracking, and you can change the values at any time after change tracking is enabled. If a database contains memory optimized tables, you can't enable change tracking with SQL Server Management Studio. You can also enable change tracking in SQL Server Management Studio by using the Database Properties (ChangeTracking Page) dialog box. (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON) The following example shows how to enable change tracking by using ALTER DATABASE. Enable Change Tracking for a Databaseīefore you can use change tracking, you must enable change tracking at the database level. This topic describes how to enable and disable change tracking for a database and a table. Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |