There are additional considerations for using datachange.
The datachange function requires all three parameters.
datachange is a measure of the inserts, deletes and updates but it does not count them individually. datachange counts an update as a delete and an insert, so each update contributes a count of 2 towards the datachange counter.
The datachange built-in returns the datachange count as a percent of the number of rows, but it bases this percentage on the number of rows remaining, not the original number of rows. For example, if a table has five rows and one row is deleted, datachange reports a value of 25 % since the current row count is 4 and the datachange counter is 1.
datachange is expressed as a percentage of the total number of rows in the table, or partition if you specify a partition. The percentage value can be greater than 100 percent because the number of changes to an object can be much greater than the number of rows in the table, particularly when the number of deletes and updates happening to a table is very high.
The value that datachange displays is the in-memory value. This can differ from the on-disk value because the on-disk value gets updated by the housekeeper, when you run sp_flushstats, or when an object descriptor gets flushed.
The datachange values is not reset when histograms are created for global indexes on partitioned tables.
Instead of consuming resources, datachange discards the descriptor for an object that is not already in the cache.
New columns are added, and their datachange value is initialized.
New partitions are added, and their datachange value is initialized.
Data-partition-specific histograms are created, deleted or updated. When this occurs, the datachange value of the histograms is reset for the corresponding column and partition.
Data is truncated for a table or partition, and its datachange value is reset
A table is repartitioned either directly or indirectly as a result of some other command, and the datachange value is reset for all the table’s partitions and columns.
A table is unpartitioned, and the datachange value is reset for all columns for the table.