Edit

Share via

INDEXPROPERTY (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL database in Microsoft Fabric

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.

Transact-SQL syntax conventions

Syntax

INDEXPROPERTY ( object_ID , index_or_statistics_name , property )

Arguments

object_ID

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

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

An expression that contains the name of the index or statistics property to return. property is varchar(128), and can be one of these values.

Note

Unless noted otherwise, NULL is returned when property isn't a valid property name, object_ID isn't a valid object ID, object_ID is an unsupported object type for the specified property, or the caller doesn't 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 isn't 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 isn't valid.
IsFulltextKey Index is the full-text and semantic indexing key for a table. 1 = True
0 = False or XML index.
NULL = Input isn't valid.
IsHypothetical Index is hypothetical and can't 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 isn't 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 isn't 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 isn't 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.
IsColumnstore Index is a memory optimized columnstore index. 1 = True
0 = False

Applies to: SQL Server 2012 (11.x) and later versions.
IsOptimizedForSequentialKey Index has optimization for last-page inserts enabled. 1 = True
0 = False

Applies to: SQL Server 2019 (15.x) and later versions.

Return types

int

Exceptions

Returns NULL on error or if a caller doesn't have permission to view the object.

A user can only view the metadata of securables that the user owns or on which the user is granted permission. This means that metadata-emitting, built-in functions such as INDEXPROPERTY might return NULL if the user doesn't have any permission on the object. For more information, see Metadata visibility configuration.

Examples

The code samples in this article use the AdventureWorks2025 or AdventureWorksDW2025 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.

A. Return properties for index on Employee table

The following example returns the values for the IsClustered, IndexDepth, and IndexFillFactor properties for the PK_Employee_BusinessEntityID index of the Employee table in the AdventureWorks2022 database.

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

Here's the result set.

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

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

B. Return properties for index on FactResellerSales table

The following example examines the properties of one of the indexes on the FactResellerSales table.

SELECT
    INDEXPROPERTY(OBJECT_ID('dbo.FactResellerSales'), 'ClusteredIndex_6d10fa223e5e4c1fbba087e29e16a7a2','IsClustered') AS [Is Clustered],
    INDEXPROPERTY(OBJECT_ID('dbo.FactResellerSales'), 'ClusteredIndex_6d10fa223e5e4c1fbba087e29e16a7a2','IsColumnstore') AS [Is Columnstore Index],
    INDEXPROPERTY(OBJECT_ID('dbo.FactResellerSales'), 'ClusteredIndex_6d10fa223e5e4c1fbba087e29e16a7a2','IndexFillFactor') AS [Fill Factor];
GO

Related content

  • Last updated on