Chapter 2: Transact-SQL Functions

derived_stat

Returns derived statistics for the specified object and index.

 derived_stat(object_name | object_id,
	index_name | index_id,
	[partition_name | partition_id,]
	“statistic”)

object_name: is the name of the object you are interested in. If you do not specify a fully qualified object name, derived_stat searches the current database.

object_id: is an alternative to object_name, and is the object ID of the object you are interested in. object_id must be in the current database

index_name: is the name of the index, belonging to the specified object that you are interested in.

index_id: is an alternative to index_name, and is the index ID of the specified object that you are interested in.

partition_name: is the name of the partition, belonging to the specific partition that you are interested in.

partition_id: is an alternative to partition_name, and is the partition ID of the specified object that you are interested in.

“statistic”

the derived statistic to be returned. Available statistics are:

Value	Returns
data page cluster ratio or dpcr	The data page cluster ratio for the object/index pair
index page cluster ratio or ipcr	The index page cluster ratio for the object/index pair
data row cluster ratio or drcr	The data row cluster ratio for the object/index pair
large io efficiency or lgio	The large I/O efficiency for the object/index pair
space utilization or sput	The space utilization for the object/index pair

Selects the space utilization for the titleidind index of the titles table:

select derived_stat("titles", "titleidind", "space utilization")

Selects the data page cluster ratio for index ID 2 of the titles table. Note that you can use either "dpcr" or "data page cluster ratio":

select derived_stat("titles", 2, "dpcr")

Statistics are reported for the entire object, as neither the partition ID nor name is not specified:

1> select derived_stat(object_id("t1"), 2, "drcr")
2> go

--------------------------- 
                   0.576923

Reports the statistic for the partition tl_928003396:

1> select derived_stat(object_id("t1"), 0, "t1_928003306", "drcr")
2> go

 --------------------------- 
                    1.000000

(1 row affected)

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 target partition; otherwise, derived_stat reports for the entire object.
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 on the third argument.

ANSI SQL – Compliance level: Transact-SQL extension.

Only the table owner can execute derived_stat.