Measures the amount of change in the data distribution since update statistics last ran. Specifically, it measures the number of inserts, updates, and deletes that have occurred on the given object, partition, or column, and helps you determine if invoking update statistics would benefit the query plan.
datachange(object_name, partition_name, column_name)
is the object name in the current database.
is the data partition name. This value can be null.
is the column name for which the datachange is requested. This value can be null.
Provides the percentage change in the au_id column in the author_ptn partition:
select datachange("authors", "author_ptn", "au_id")
Provides the percentage change in the authors table on the au_ptn partition. The null value for the column_name parameter indicates that this checks all columns that have historgram statistics and obtains the maximum datachange value from among them.
select datachange("authors", "au_ptn", null)
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.
datachange is reset or initialized to zero when:
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.
datachange has the following restrictions:
datachange statistics are not maintained on tables in system tempdbs, user-defined tempdbs, system tables, or proxy tables.
datachange updates are non-transactional. If you roll back a transaction, the datachange values are not rolled back, and these values can become inaccurate.
If memory allocation for column-level counters fails, Adaptive Server tracks partition-level datachange values instead of column-level values.
If Adaptive Server does not maintain column-level datachange values, it then resets the partition-level datachange values whenever the datachange values for a column are reset.
Any user can execute datachange.
