Jaa

INDEXPROPERTY (Transact-SQL)

  • Artikkeli

Returns the named index or statistics property value of a specified table identification number, index or statistics name, and property name. Returns NULL for XML indexes.

Topic link iconTransact-SQL Syntax Conventions

Syntax

INDEXPROPERTY ( object_ID , index_or_statistics_name , property )

Arguments

  • object_ID
    Is an expression that contains the object identification number of the table or indexed view for which to provide index property information. object_ID is int.
  • index_or_statistics_name
    Is an expression that contains the name of the index or statistics for which to return property information. index_or_statistics_name is nvarchar(128).

  • property
    Is an expression that contains the name of the database property to return. property is varchar(128), and can be one of these values.

    Note

    Unless noted otherwise, NULL is returned when property is not a valid property name, object_ID is not a valid object ID, object_ID is an unsupported object type for the specified property, or the caller does not have permission to view the object's metadata.

    Property Description Value

    IndexDepth

    Depth of the index.

    Number of index levels.

    NULL = XML index or input is not valid.

    IndexFillFactor

    Fill factor value used when the index was created or last rebuilt.

    Fill factor

    IndexID

    Index ID of the index on a specified table or indexed view.

    Index ID

    IsAutoStatistics

    Statistics were generated by the AUTO_CREATE_STATISTICS option of ALTER DATABASE.

    1 = True

    0 = False or XML index.

    IsClustered

    Index is clustered.

    1 = True

    0 = False or XML index.

    IsDisabled

    Index is disabled.

    1 = True

    0 = False

    NULL = Input is not valid.

    IsFulltextKey

    Index is the full-text key for a table.

    1 = True

    0 = False or XML index.

    NULL = Input is not valid.

    IsHypothetical

    Index is hypothetical and cannot be used directly as a data access path. Hypothetical indexes hold column-level statistics and are maintained and used by Database Engine Tuning Advisor.

    1 = True

    0 = False or XML index

    NULL = Input is not valid.

    IsPadIndex

    Index specifies space to leave open on each interior node.

    1 = True

    0 = False or XML index.

    IsPageLockDisallowed

    Page-locking value set by the ALLOW_PAGE_LOCKS option of ALTER INDEX.

    1 = Page locking is disallowed.

    0 = Page locking is allowed.

    NULL = Input is not valid.

    IsRowLockDisallowed

    Row-locking value set by the ALLOW_ROW_LOCKS option of ALTER INDEX.

    1 = Row locking is disallowed.

    0 = Row locking is allowed.

    NULL = Input is not valid.

    IsStatistics

    index_or_statistics_name is statistics created by the CREATE STATISTICS statement or by the AUTO_CREATE_STATISTICS option of ALTER DATABASE.

    1 = True

    0 = False or XML index.

    IsUnique

    Index is unique.

    1 = True

    0 = False or XML index.

Return Types

int

Exceptions

Returns NULL on error or if a caller does not have permission to view the object.

In SQL Server 2005, a user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as INDEXPROPERTY may return NULL if the user does not have any permission on the object. For more information, see Metadata Visibility Configuration and Troubleshooting Metadata Visibility.

Examples

The following example returns the values for the IsClustered, IndexDepth, and IndexFillFactor properties for the PK_Employee_EmployeeID index of the Employee table.

USE AdventureWorks;
GO
SELECT 
    INDEXPROPERTY(OBJECT_ID('HumanResources.Employee'),
        'PK_Employee_EmployeeID','IsClustered')AS [Is Clustered],
    INDEXPROPERTY(OBJECT_ID('HumanResources.Employee'),
        'PK_Employee_EmployeeID','IndexDepth') AS [Index Depth],
    INDEXPROPERTY(OBJECT_ID('HumanResources.Employee'),
        'PK_Employee_EmployeeID','IndexFillFactor') AS [Fill Factor];
GO

Here is the result set:

Is Clustered Index Depth Fill Factor 
------------ ----------- ----------- 
1            2           0

(1 row(s) affected)

See Also

Reference

CREATE INDEX (Transact-SQL)
sys.indexes (Transact-SQL)
sys.index_columns (Transact-SQL)
sys.stats (Transact-SQL)
sys.stats_columns (Transact-SQL)

Other Resources

Index Statistics
Troubleshooting Metadata Visibility

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
New content:
  • Added the Exceptions section.