INDEX_ESTIMATE function [Miscellaneous]

Returns selectivity estimates from the index as a percentage calculated by the query optimizer, based on specified parameters.

Syntax
INDEX_ESTIMATE( column-name [ , value [ , relation-string ] ] )
Parameters
  • column-name   The column used in the estimate.

  • value   The value to which the column is compared. The default is NULL.

  • relation-string   The comparison operator used for the comparison, enclosed in single quotes. Possible values for this parameter are: '=' , '>' , '<' , '>=' , '<=' , '<>' , '!=' , '!<' , and '!>'. The default is '='.

Returns

REAL

Remarks

If value is NULL then the relation strings '=' and '!=' are interpreted as the IS NULL and IS NOT NULL conditions, respectively.

See also
Standards and compatibility
  • SQL/2003   Vendor extension.

Example

The following statement returns the percentage of EmployeeID values estimated to be greater than 200.

SELECT INDEX_ESTIMATE( EmployeeID, 200, '>' )
FROM Employees;