Returns derived statistics for the specified object and index.


derived_stat("object_name" | object_id,
	index_name | index_id,
	["partition_name" | partition_id,]




  • derived_stat returns a double precision value.

  • The values returned by derived_stat match the values presented by the optdiag utility.

  • If the specified object or index does not exist, derived_stat returns NULL.

  • Specifying an invalid statistic type results in an error message.

  • Using the optional partition_name or partition_id reports the requested statistic for the target partition; otherwise, derived_stat reports the statistic for the entire object.

  • Instead of consuming resources, derived_stat discards the descriptor for an object that is not already in the cache.

  • If you provide:

    • Four arguments – derived_stat uses the third argument as the partition, and returns derived statistics on the fourth argument.

    • Three arguments – derived_stat assumes you did not specifiy a partition, and returns derived statistic specified by the third argument.

See also:
  • Access Methods and Query Costing for Single Tables and Statistics Tables and Displaying Statistics with optdiag in Performance and Tuning Guide

  • optdiag in Utility Guide


ANSI SQL – Compliance level: Transact-SQL extension.


The permission checks for derived_stat differ based on your granular permissions settings.

Granular PermissionsDescription

With granular permissions enabled, you must be the table owner or have manage database permission to execute derived_stat


With granular permissions disabled, you must be the table owner or be a user with sa_role to execute derived_stat.